Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0
От | Geoff Winkless |
---|---|
Тема | Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0 |
Дата | |
Msg-id | CAEzk6fcT3DH5jDc_c1gL=o=Y3yb07gvBJeaspuO+XTSi7H7zCg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0 (Andres Freund <andres@anarazel.de>) |
Ответы |
Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0
(Petr Jelinek <petr@2ndquadrant.com>)
Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0 (Andres Freund <andres@anarazel.de>) |
Список | pgsql-hackers |
<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>
В списке pgsql-hackers по дате отправления: