Question 168687 in PostgreSQL Question posted in
The official documentation can be found here.

Can Postgresql restrict updates using upsert for new values that don’t match the original values?

I want to update a table using another table which has the same schema as the table I want to update.I tried to restrict the updates for values that are different from the values that already exist in the table, but I’m not sure this syntax is correct

Example:

CREATE TABLE your_table (
  id SERIAL PRIMARY KEY,
  column1 TEXT,
  column2 INTEGER,
  column3 FLOAT,
  unique_column TEXT UNIQUE
);

INSERT INTO your_table (column1, column2, column3, unique_column)
VALUES
  ('apple', 1, 10.5, 'apple_1'),
  ('banana', 2, 20.0, 'banana_2'),
  ('orange', 3, 30.5, 'orange_3'),
  ('apple', 4, 40.0, 'apple_4'),
  ('banana', 5, 50.5, 'banana_5'),
  ('orange', 6, 60.0, 'orange_6');

Now UPSERT

    INSERT INTO your_table (column1, column2, column3)
    SELECT value1, value2, value3
    FROM another_table
    ON CONFLICT (unique_column)
    DO UPDATE SET
        column1 = EXCLUDED.column1,
        column2 = EXCLUDED.column2,
        column3 = EXCLUDED.column3
    WHERE your_table.column1 IS DISTINCT FROM EXCLUDED.column1 
        OR your_table.column2 IS DISTINCT FROM EXCLUDED.column2 
        OR your_table.column3 IS DISTINCT FROM EXCLUDED.column3;

Does this upset will only update values for (column1,column2,column3) in your_table if values from another_table are different than your_table ? In case of inserts the keys that do not match in (unique_column), the particular row will be inserted right ?

Tags:

2

Answers


  1. 0

    Does this upset will only update values for (column1,column2,column3)
    in your_table if values from another_table are different than
    your_table ?

    Yes, it will update if any of those columns have different values in your_table.

    In case of inserts the keys that do not match in (unique_column), the
    particular row will be inserted right ?

    Yes

    Login or Signup to reply.
  2. 0

    No, this will always insert a row; you will never get on conflict. You use column unique_column as your unique determinant, however that column is not listed for the insert and is therefore always null. Null never match raises on conflict. See demo here. You must supply an appropriate value for column unique_column:

    insert into your_table (column1, column2, column3,unique_column)
    select value1, value2, value3, some_value_here 
    from another_table
    on conflict (unique_column) ...

    Then the answer by @lokeshkumar will be correct.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search