11

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?

Vova
  • 563
  • 8
  • 20
  • 6
    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 Sep 08 '18 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 Sep 08 '18 at 14:45

1 Answers1

1

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509