UPSERT On Conflict with no target
(1) By ddevienne on 2020-03-31 13:46:48 [link] [source]
From UPSERT doc
[...] The conflict target specifies a specific uniqueness constraint that will trigger the upsert.
The conflict target is required for DO UPDATE upserts, but is optional for DO NOTHING.
When the conflict target is omitted, the upsert behavior is triggered by a violation of any
uniqueness constraint on the table of the INSERT. [...]
My table has a single (3-column) UNIQUE constraint, and no other constraints.
insert ... on conflict do update set ... fails with a syntax error.
insert ... on conflict(col1, col2, col3) do update set ... is OK.
I'm obviously misreading the When the conflict target is omitted sentence.
What am I missing? That somehow applies to DO NOTHING only? The previous
sentence does say required for DO UPDATE, but the next one read independently to me.
In any case, I find this confusing as written, FWIW. Thanks, --DD
(2) By David Raymond (dvdraymond) on 2020-03-31 14:19:34 in reply to 1 [source]
If you're going to do an update, then you need a specific constraint.
If you're going to do nothing, then you can either specify a constraint, or leave it blank to mean any constraint.
It's because one insert row could violate multiple different unique constraints, with it being a different row for each of the constraints that's causing the violation, and the upsert is only intended to update a single row.
But if you're not going to update a row and just "do nothing" then it's ok to say "for any constraint", because the action would be the same for all of them without any ambiguity.