Multiple on conflict targets

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?

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:

• How to include excluded rows in RETURNING from INSERT … ON CONFLICT

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.

StackExchange Database Administrators Q#206185, answer score: 15