How to upsert in Postgres on conflict on one of 2 columns?

Question

Is it possible to do upsert in Postgres 9.5 when conflict happens on one of 2 columns in a table.? Basically I have 2 columns and if either column throws unique constraint violation, then I would like to perform update operation.

Westfalen

Answer 1

Yes, and this behaviour is default. Any unique constraint violation constitutes a conflict and then the UPDATE is performed if ON CONFLICT DO UPDATE is specified. The INSERT statement can have only a single ON CONFLICT clause, but the conflict_target of that clause can specify multiple column names each of which must have an index, such as a UNIQUE constraint. You are, however, limited to a single conflict_action and you will not have information on which constraint caused the conflict when processing that action. If you need that kind of information, or specific action depending on the constraint violation, you should write a trigger function but then you lose the all-important atomicity of the INSERT ... ON CONFLICT DO ... statement.

Phonetist

I'm not sure this is 100% correct, admittedly the question is vague. If the 2 columns have different unique constraints (what I understood from the question), then the ON CONFLICT can only be used for DO NOTHING. You can't seem to chain or "disjunction" the so-called conflict_target. – Weinstein

@Weinstein Expanded my answer. – Phonetist

Thanks for your update. It answers my own question about what whether or not we can handle two different violations with the same INSERT. – Weinstein

Answer 2

I think in Postgres 9.5 ON CONFLICT can have only one constraint or multiple column names but on that multiple columns must have combine one index

Laufer