postgresql ON CONFLICT with multiple constraints

Question

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?

In this use-case you could just not specify any constraints (ie ON CONFLICT DO NOTHING). In the general case (for instance doing nothing on 2 out of 3 constrains, or on 1 do nothing and the other one do an update), you would need to be able to know at planning-time whether those conflict or not (eg do you update or do you ignore). This, as far as I know, is not implemented (nor planned to be). — Marth
– Marth, Commented Sep 8, 2018 at 14:39
for my case i need to ignore all conflicts. Connecting via psycopg2 i can solve it via try: query; except IntegrityError: rollback, but seems this is bad solution, and there is probably sql way without using conflict statement, if conflict statement cannot resolve multiple constraint handling — Vova
– Vova, Commented Sep 8, 2018 at 14:45

Lukas Eder · Accepted Answer · 2022-05-07 08:28:12Z

2

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.

answered May 7, 2022 at 8:28

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql ON CONFLICT with multiple constraints

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related