Equivalent of ON CONFLICT DO NOTHING for UPDATE postgres

Question

I want to update rows in my postgres database if the updated version wouldn't violate the primary key constraint. If it would, I want to leave the row as it is.

Assuming the table has primary keys on col1, col2, col3, if I run a query like this:

UPDATE table SET (col1, col2) = ('A', 'B') 
      WHERE col1='D' AND col2='E';

The query will fail and I will get a duplicate key error if there exists two entries:

'A', 'B', 'C'
'D', 'E', 'C'

i.e col3 is the same between an existing row and a row to be updated.

If I was INSERTing rows I would use ON CONFLICT DO NOTHING but I can't find an implementation of this for UPDATE. Does an equivalent exist?

Answer 1

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).

Answer 2

AFAIK, there is no such equivalent.

Let us say you are developing an application that connects to a postgresql database, there are a few things you need to keep in mind, in the context of your question:

• It may be counter-intuitive but you should consider errors being thrown by the DB as a good thing.
  This is just about getting a status, it does not mean application crash.
• For insert, there is an alternative choice of action on conflict (update or nothing) so it makes sense to have a syntax to let you decide.
  For updates, the only thing you can do is ... nothing.
  So why would SQL let you ask to do something specific since there is no choice? Remember that DB reporting errors is good, so let the DB do nothing and tell you why.
• Last, it is a bad practice to update primary keys.
  The ON CONFLICT ... for inserts is not intended to update the primary key fields. The very opposite in fact: it is intended to update all the fields except the ones from the primary key in a single record.

While I am on that point, please note that there was no need for a conflict on primary key for the query to fail
1 record with the "convenient" ON UPDATE NO ACTION foreign key would have made it fail too (which is still better than updating 10M+ records in 50 tables with a ON UPDATE CASCADE ...). BTW, did you know Oracle does not even have the ON UPDATE CASCADE clause? What do you think is the reason for that?

---

What can you/should not do in that situation?

1. Do not update the primary key, like I said. Your question is still valid for UNIQUE constraints but please please please, NEVER update primary keys.
2. Do not attempt to see if a conflicting record already exists. It may take a long time and still be unreliable.
   Do you really want to select millions of records just to avoid the error codes?
   Also, when you extend to other constraints (CHECK or EXCLUSION), will you really type the additional code it takes with no error in order to, once again, only avoid an error code?
   Last, if you have implemented row-level security, the conflict may arise from a record you cannot see.
3. Handle the error code in your app. Receiving status is GOOD.
4. Use save points if you are in the middle of a transaction.
   This is the only annoying thing with DB errors: if you get one in the middle of a transaction, you will start getting current transaction is aborted, commands ignored until end of transaction block for everything.
   Hopefully, you do not need to roll the entire transaction back and redo everything from scratch. You can get away using the following piece of code.

Here you go:

BEGIN;
SAVEPOINT MySavepoint;
UPDATE mytable set myuniquefield = 3; /*2+ records are going to be updated */
rollback to savepoint MySavepoint;
/*Insert Some more queries here*/
COMMIT;