Equivalent of ON CONFLICT DO NOTHING for UPDATE postgres

by

You can use a correlated subquery with a WHERE NOT EXISTS clause to ensure that your update will not generate duplicates, like :

UPDATE mytable t
SET (col1, col2) = ('AAA', 'BBB')
WHERE t.col1 = 'AAB' and t.col2 = 'BBA'
AND NOT EXISTS (
   SELECT 1 FROM mytable WHERE col1 = 'AAA' AND col2 = 'BBB' AND col3 = t.col3
);

Tested in this db fiddle.

As commented by Roman Konoval, please note that this would still generate duplicate key error if a concurrent transaction inserts the same key while the UPDATE is running. This pinpoints that updating the primary key of a table is not a good practice (see the below answer from @Lau for a detailed discussion on this matter).

Leave a Comment