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?

Solution 1

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;

Solution 2

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

Comments

[a_horse_with_no_name]

If you know it exists, you could simply turn that into an INSERT

[Roman-Stop RU aggression in UA]

This would still generate duplicate key error when one transaction updates key to the value that does not exits but concurrent transaction inserts the key

[GMB]

@RomanKonoval : correct. I edited my post to include this important information. Thanks !

[BHC]

The latter half of this answer is good. Checking for existence before updating would take far too much time on my database which has in the billions of rows. To resolve my issue, I updated on a table with no primary keys then inserted (with ON CONFLICT) into a new table with primary keys. This way I didn't have to worry about handling error codes, but your suggestions are good if you couldn't do this.

[lmat - Reinstate Monica]

NEVER update primary keys? Do you also advise NEVER use natural primary keys? I mean, what if the name of the hotel changes?

[Dynom]

Duplicated errors can occur with any unique() constraint, it's not limited to PKs. So the comment still holds, but I don't think it should be as a "bad practice". If you want guarantees, you can use locks or just deal with the concurrency by using smaller batch sizes or limit the set with a more fine-grained WHERE if you can.

[juanm55]

That sounds as if you should not be using the name of the hotel as primary key... what if a competitor opens a hotel with the same branding as the initial one just accross the street? IMO you should be referencing that to Tax registration number or something that doesn't change unles it's a company change (in which case, it would be a new record)