6

I have two unique indexes on columns a and b. I need something like this:

insert into my_table (a, b) values (1, 2), (1, 2)
on conflict (a) do update set c = 'a_violation'
on conflict (b) do update set c = 'b_violation'

So generally I want to make different update based on conflict target - syntax above is not supported (only one on conflict statement is supported). Is there any other way to do this?

  • Have you considered a trigger? – McNets May 8 '18 at 19:21
  • 2
    There are many ways. The best one depends on your undisclosed requirements. The outstanding virtue of ON CONFLICT is to handle race conditions under concurrent write load reliably ... – Erwin Brandstetter May 9 '18 at 2:33
  • @ErwinBrandstetter Could you please elaborate on what any of these many ways are? – Eliecer Thoms Dec 22 '18 at 2:48
  • @EliecerThoms: I didn't add an answer because the question is ambiguous in multiple ways. Depending on actual requirements, different answers apply. I elaborated now, mostly to illustrate why you wouldn't want to go there at all. – Erwin Brandstetter Dec 28 '18 at 0:44
5

Your example suggests duplicate rows in the VALUES clause itself - which would result in:

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

Can be solved by folding duplicates in the input. See:

But a related problem affects your outlined query as a whole, and that's not as easy to overcome.

What to do if the same input row triggers multiple unique violations?
What to do if multiple input rows trigger the same unique violation?
What to do if multiple input rows trigger distinct unique violations of the same target row?
And combinations thereof.

Postgres developers probably didn't want to open this can of worms and restricted the UPSERT feature to a single constraint.

ON CONFLICT DO NOTHING - without conflict target - works for any applicable violation. The alternative action for this variant ("do nothing") is unambiguous.

You would have to be a lot more specific (also about concurrency and possible write load) to get a more specific answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.