Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

<div dir="ltr"><div class="gmail_default" style="font-family:verdana,sans-serif;font-size:small">Apologies for butting
inbut can I (as a user) express a preference as a user against DO? </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">Firstly,it looks horrible. And what's to stop me having "SELECT
trueAS do" in the where clause (as per your UPDATE objection)?</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">Shouldn'tUPDATE be a reserved keyword anyway? AIUI ANSI suggests
so.</div><divclass="gmail_default" style="font-family:verdana,sans-serif;font-size:small"><br /></div><div
class="gmail_default"style="style"><font face="verdana, sans-serif"><a
href="http://developer.mimer.se/validator/sql-reserved-words.tml">http://developer.mimer.se/validator/sql-reserved-words.tml</a></font><br
/></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">I had always assumed it was; anyone who
produceda query for me that contained update in an unusual context would get slapped heavily.</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">Geoff</div></div><divclass="gmail_extra"><br /><div
class="gmail_quote">On23 April 2015 at 11:54, Andres Freund <span dir="ltr"><<a href="mailto:andres@anarazel.de"
target="_blank">andres@anarazel.de</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex">On 2015-04-22 16:40:07 -0700, Peter Geoghegan wrote:<br /> > On
Wed,Apr 22, 2015 at 3:23 PM, Peter Geoghegan <<a href="mailto:pg@heroku.com">pg@heroku.com</a>> wrote:<br /> >
>* We need to sort out those issues with the grammar, since that only<br /> > > really applies to the
inferencespecification. Maybe the WHERE clause<br /> > > that the inference specification accepts can be broken
out.No ON<br /> > > CONFLICT UPDATE specific issues left there, AFAICT though.<br /> ><br /> > I pushed
somecode that deals with the predicate being within parenthesis:<br /> ><br /> > <a
href="https://github.com/petergeoghegan/postgres/commit/358854645279523310f998dfc9cb3fe3e165ce1e"
target="_blank">https://github.com/petergeoghegan/postgres/commit/358854645279523310f998dfc9cb3fe3e165ce1e</a><br/><br
/>And the way you've used nonassoc here doesn't look correct. You're<br /> hiding legitimate ambiguities in the
grammar.UPDATE is a unreserved<br /> keyword, so for<br /><br /> ... ON CONFLICT '(' index_params ')' where_clause
OnConflictUpdateStmt<br/><br /> it won't be able to discern whether an UPDATE in the WHERE clause is<br /> part of the
where_clauseor OnConflictUpdate.<br /><br /> This is legal:<br /> SELECT * FROM (SELECT true as update) f WHERE
update;<br/> i.e. 'update' can be the last part of a WHERE clause.<br /><br /> Essentially what you're trying to do
withthe nonassic is hiding that<br /> UPDATE and IGNORE need to be reserved keywords with the syntax you're<br />
proposing.We can either make them reserved or change the syntax.<br /><br /> One way to avoid making them reserved
keywords- which would be somewhat<br /> painful - is to add a 'DO' before the IGNORE/UPDATE. I.e. something like<br
/><br/>   ON CONFLICT opt_conflict_expr DO OnConflictUpdateStmt<br /> | ON CONFLICT opt_conflict_expr DO IGNORE<br
/><br/> Greetings,<br /><br /> Andres Freund<br /><span class="HOEnZb"><font color="#888888"><br /><br /> --<br /> Sent
viapgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br /> To
makechanges to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></font></span></blockquote></div><br /></div>