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
