INSERT ... ON CONFLICT DO UPDATE with _any_ constraint - Mailing list pgsql-hackers

<div dir="ltr"><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:small">I finally got around
torunning some UPSERT tests on the development build, which is very exciting for me :)</div><div class="gmail_default"
style="font-family:verdana,sans-serif;font-size:small"><br/></div><div class="gmail_default"
style="font-family:verdana,sans-serif;font-size:small">I'mnot sure if I missed the point with this (probably...): I'm
unclearon the reason why DO UPDATE requires explicitly specifying the constraint while DO NOTHING does not. </div><div
class="gmail_default"style="font-family:verdana,sans-serif;font-size:small"><br /></div><div class="gmail_default"
style="font-family:verdana,sans-serif;font-size:small">Ifit's a feature of the locking implementation (or something)
thatfor "DO UPDATE" only one index can be used, then so be it.</div><div class="gmail_default"
style="font-family:verdana,sans-serif;font-size:small"><br/></div><div class="gmail_default"
style="font-family:verdana,sans-serif;font-size:small">Howeverif it would be possible to allow any conflict to run the
UPDATEclause (in the same way as any conflict triggers DO NOTHING in the alternate form) I would personally find that
verypleasant. </div><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:small"><br /></div><div
class="gmail_default"style="font-family:verdana,sans-serif;font-size:small">You could even then arbitrate on conflicts
inthe UPDATE clause, if you had to, using (say)</div><div class="gmail_default"
style="font-family:verdana,sans-serif;font-size:small"><br/></div><div class="gmail_default"
style="font-size:small"><fontface="monospace, monospace">INSERT INTO mytable ... </font></div><div
class="gmail_default"style="font-size:small"><font face="monospace, monospace">ON CONFLICT DO UPDATE
SET </font></div><divclass="gmail_default" style="font-size:small"><font face="monospace, monospace"> 
col1=CASE </font></div><divclass="gmail_default" style="font-size:small"><font face="monospace, monospace">    WHEN
mytable.uniquefield1=excluded.uniquefield1THEN </font><span
style="font-family:monospace,monospace">targettedvalue1 </span></div><divclass="gmail_default"
style="font-size:small"><fontface="monospace, monospace">    ELSE </font><span
style="font-family:monospace,monospace">mytable.col1 </span></div><divclass="gmail_default"
style="font-size:small"><fontface="monospace, monospace">  END,</font></div><div class="gmail_default"
style="font-size:small"><fontface="monospace, monospace">  col2=CASE </font></div><div class="gmail_default"
style="font-size:small"><fontface="monospace, monospace">    WHEN mytable.uniquefield2=excluded.uniquefield2
THEN </font><spanstyle="font-family:monospace,monospace">targettedvalue2 </span></div><div class="gmail_default"
style="font-size:small"><fontface="monospace, monospace">  ELSE </font><span
style="font-family:monospace,monospace">mytable.col2 </span></div><divclass="gmail_default"
style="font-size:small"><fontface="monospace, monospace">  END;</font></div><div class="gmail_default"
style="font-family:verdana,sans-serif;font-size:small"><br/></div><div class="gmail_default"
style="font-family:verdana,sans-serif;font-size:small">Notexactly pretty but workable.</div><div class="gmail_default"
style="font-family:verdana,sans-serif;font-size:small"><br/></div><div class="gmail_default"
style="font-family:verdana,sans-serif;font-size:small">Ijust 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
uniquefields.</div><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:small"><br /></div><div
class="gmail_default"style="font-family:verdana,sans-serif;font-size:small">In the event that the INSERT triggers a
constraintthat the UPDATE fails to resolve, it will still fail in exactly the same way that running the ON CONFLICT on
aspecific constraint would fail, so it's not like you gain any extra value from specifying the constraint, is
it?</div><divclass="gmail_default" style="font-family:verdana,sans-serif;font-size:small"><br /></div><div
class="gmail_default"style="font-family:verdana,sans-serif;font-size:small">As I said, I probably missed the
point.</div><divclass="gmail_default" style="font-family:verdana,sans-serif;font-size:small"><br /></div><div
class="gmail_default"style="font-family:verdana,sans-serif;font-size:small">Geoff<br /></div></div>