28

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?

Improve this question
4
  • Have you considered a trigger? Commented May 8, 2018 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 ... Commented May 9, 2018 at 2:33
  • 1
    @ErwinBrandstetter Could you please elaborate on what any of these many ways are? Commented Dec 22, 2018 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. Commented Dec 28, 2018 at 0:44

1 Answer 1

Reset to default
19

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.

Improve this answer
1
  • 1
    "ON CONFLICT DO NOTHING - without conflict target - works for any applicable violation. The alternative action for this variant ("do nothing") is unambiguous." This solved my problem. Thanks Commented May 7, 2024 at 12:49

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.