Postgres on Conflict Do Update on Composite Primary Keys

Postgres on conflict do update on composite primary keys

Just place both keys in the ON CONFLICT clause:

INSERT INTO answer VALUES (1,1,'q1') 
ON CONFLICT (person_id,question_id) 
DO UPDATE SET answer = EXCLUDED.answer; 

Example:

INSERT INTO answer VALUES (1,1,'q1') 
ON CONFLICT (person_id,question_id) 
DO UPDATE SET answer = EXCLUDED.answer;             

SELECT * FROM answer;
 person_id | question_id | answer 
-----------+-------------+--------
         1 |           1 | q1
(1 Zeile)

INSERT INTO answer VALUES (1,1,'q1') 
ON CONFLICT (person_id,question_id) 
DO UPDATE SET answer = EXCLUDED.answer || '-UPDATED';             

SELECT * FROM answer;
 person_id | question_id |   answer   
-----------+-------------+------------
         1 |           1 | q1-UPDATED
(1 Zeile)

Demo: db<>fiddle

PostgreSQL 15 +

You can also achieve the same result using MERGE:

MERGE INTO answer i
  -- records to be inserted
  USING (
    VALUES (1,1,'q1'), -- already exists in table answers!
           (2,2,'q2')  -- new record
  ) AS j (person_id, question_id, answer)
  -- checking if the PK of given records (j) already exists
  -- in table "answer" (i).
  ON j.question_id = i.question_id AND j.person_id = i.person_id
WHEN MATCHED THEN
  -- in case of a match (conflict), I want to add the suffix '-UPDATED' 
  -- to the column "answer"
  UPDATE SET answer = j.answer || '-UPDATED'
WHEN NOT MATCHED THEN 
  -- if there is no match (conflict) just INSERT the record.
  INSERT (person_id, question_id, answer)
  VALUES (j.person_id, j.question_id, j.answer);

Demo: db<>fiddle

Upsert if on conflict occurs on multiple columns in Postgres db

Actually, found it here but not in the post marked as answer but the most rated post. Use multiple conflict_target in ON CONFLICT clause

So our query will be as follows:

INSERT into example (col1, col2, col3)
VALUES (1, 2, 3)
ON CONFLICT (col1, col2) DO UPDATE 
SET col3 = 42

SQL query to update a column in a composite key

... if the key combination is already present then ignore ...

If ignore means: don't update, then you could use an exists(tuple_with _new_values in the same table), like below:

---

UPDATE my_table mt
SET B_id = 10
WHERE mt.B_id = 20
AND NOT EXISTS ( SELECT *
        FROM my_table nx        -- same table
        WHERE nx.A_id = mt.A_id -- same value
        AND nx.B_id = 10        -- new value
        AND nx.C_id = mt.C_id   -- same value
        );

---

[UPDATE] After the change in the question. You could use a CTE to combine two operations:

• first: delete the records for which an update would conflict
• second: update the records that were not deleted

---

WITH del AS ( -- delete tuples for which UPDATE would cause a conflict
        DELETE FROM my_table mt
        WHERE mt.B_id = 20
        AND EXISTS ( SELECT *
                FROM my_table nx
                WHERE nx.A_id = mt.A_id
                AND nx.B_id = 10
                AND nx.C_id = mt.C_id
                )
        RETURNING *
        )
UPDATE my_table upd -- UPDATE the records that were not deleted
SET B_id = 10
WHERE upd.B_id = 20
AND NOT EXISTS ( SELECT *
        FROM del
        WHERE del.A_id = upd.A_id
        AND del.B_id = upd.B_id
        AND del.C_id = upd.C_id
        );

INSERT/UPDATE with CONFLICT with Autoincrement Postgres

That sounds to me like you should create a unique constraint with two columns:

ALTER TABLE answers ADD UNIQUE (report_id, question_id);

Then you can use that constraint with ON CONFLICT:

INSERT INTO answers (question_id, report_id, value)
VALUES (1, 2, 'gewonnen')
ON CONFLICT (report_id, question_id) DO UPDATE
SET value = EXCLUDED.value;

Note that you don't need a WHERE clause, since this is only about the conflicting row anyway.

Also note that I did not specify id, so the autogenerated value is used.

INSERT ON CONFLICT DO UPDATE SET (an UPSERT) statement with a unique constraint is generating constraint violations when run concurrently

After reading through the Postgres source code I believe I have found a satisfying answer to this.

In the ON CONFLICT statements given, only id is mentioned as a column. This will cause Postgres to use test_table_pkey as an arbiter index.

Normally, when evaluating an ON CONFLICT Postgres will speculatively insert the new row, and check only the arbiter indexes for conflicts (against currently committed rows) using ExecCheckIndexConstraints. If there is a conflict on test_table_pkey and test_table_uc it will only be checking for the arbiter index test_table_pkey, which will have a conflict and cause Postgres to revert to the ON CONFLICT clause.

If two of these INSERT expressions are occurring at the same time, neither will see each other (as they are both speculative and both pass ExecCheckIndexConstraints). This will cause Postgres to attempt to commit both speculative rows. The first row will commit properly. The second row will cause two constraint violations during ExecInsertIndexTuples. The function ExecInsertIndexTuples will suppress errors on any arbiter indexes only, meaning that the constraint violation on test_table_pkey will get caught (and cause Postgres to retry the insert from the top), but the test_table_uc violation will become an uncaught error which causes the statement to error.

Essentially, this means if your upsert is potentially causing conflicts on constraints that are not named in the ON CONFLICT clause, these statements will fail, but only during concurrent inserts.

---

Related Topics