I finally got around to running some UPSERT tests on the development build,
which is very exciting for me :)
I'm not sure if I missed the point with this (probably...): I'm unclear on
the reason why DO UPDATE requires explicitly specifying the constraint
while DO NOTHING does not.
If it's a feature of the locking implementation (or something) that for "DO
UPDATE" only one index can be used, then so be it.
However if it would be possible to allow any conflict to run the UPDATE
clause (in the same way as any conflict triggers DO NOTHING in the
alternate form) I would personally find that very pleasant.
You could even then arbitrate on conflicts in the UPDATE clause, if you had
to, using (say)
INSERT INTO mytable ...
ON CONFLICT DO UPDATE SET
col1=CASE
WHEN mytable.uniquefield1=excluded.uniquefield1 THEN targettedvalue1
ELSE mytable.col1
END,
col2=CASE
WHEN mytable.uniquefield2=excluded.uniquefield2 THEN targettedvalue2
ELSE mytable.col2
END;
Not exactly pretty but workable.
I just find it slightly upsetting that for (what I would expect is) the
majority use case (when the INSERT would only ever trigger one unique
constraint) one must still define the unique fields.
In the event that the INSERT triggers a constraint that the UPDATE fails to
resolve, it will still fail in exactly the same way that running the ON
CONFLICT on a specific constraint would fail, so it's not like you gain any
extra value from specifying the constraint, is it?
As I said, I probably missed the point.
Geoff
