postgresql ON CONFLICT with multiple constraints

user_9067016 0
Viewed 2254

There is a constraint violation handling "On conflict" statement, working fine if i want to check 1 (!) constraint

For example :

 INSERT INTO my_table (co1,col2..colN) 
 VALUES (...) 
 ON CONFLICT (col1, col2) DO NOTHING --or update

But if i have 2 constaints unique(col1,col2) and unique(col5,col6,col7) , the query below is not working :

INSERT INTO my_table (co1,col2..colN) 
VALUES (...) 
ON CONFLICT (col1, col2) DO NOTHING --or update
ON CONFLICT (col5, col6, col7) DO NOTHING --or update

This raises the error, pointing on : ERROR: syntax error at or near "on". LINE _: on conflict (col5, col6, col7) do nothing

How could i resolve using multiple constraint checking in one query?

constraints postgresql
1 Answers

As per the documentation:

For ON CONFLICT DO NOTHING, it is optional to specify a conflict_target; when omitted, conflicts with all usable constraints (and unique indexes) are handled. For ON CONFLICT DO UPDATE, a conflict_target must be provided.

So, you can just write:

INSERT INTO my_table (co1,col2..colN) 
 VALUES (...) 
 ON CONFLICT DO NOTHING

This doesn't work for DO UPDATE, though. However, we might soon get treated to the standard SQL MERGE statement in PostgreSQL, in case of which you could do these more complex conflict resolutions manually.

user_5217990
Related