Equivalent of ON CONFLICT DO NOTHING for UPDATE postgres
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.
TheON 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?
- 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. - 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
orEXCLUSION
), 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. - Handle the error code in your app. Receiving status is GOOD.
- 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 gettingcurrent 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;
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).