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

Apologies for butting in but can I (as a user) express a preference as a
user against DO?

Firstly, it looks horrible. And what's to stop me having "SELECT true AS
do" in the where clause (as per your UPDATE objection)?

Shouldn't UPDATE be a reserved keyword anyway? AIUI ANSI suggests so.

http://developer.mimer.se/validator/sql-reserved-words.tml

I had always assumed it was; anyone who produced a query for me that
contained update in an unusual context would get slapped heavily.

Geoff

On 23 April 2015 at 11:54, Andres Freund <andres(at)anarazel(dot)de> wrote:

> On 2015-04-22 16:40:07 -0700, Peter Geoghegan wrote:
> > On Wed, Apr 22, 2015 at 3:23 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> > > * We need to sort out those issues with the grammar, since that only
> > > really applies to the inference specification. Maybe the WHERE clause
> > > that the inference specification accepts can be broken out. No ON
> > > CONFLICT UPDATE specific issues left there, AFAICT though.
> >
> > I pushed some code that deals with the predicate being within
> parenthesis:
> >
> >
> https://github.com/petergeoghegan/postgres/commit/358854645279523310f998dfc9cb3fe3e165ce1e
>
> And the way you've used nonassoc here doesn't look correct. You're
> hiding legitimate ambiguities in the grammar. UPDATE is a unreserved
> keyword, so for
>
> ... ON CONFLICT '(' index_params ')' where_clause OnConflictUpdateStmt
>
> it won't be able to discern whether an UPDATE in the WHERE clause is
> part of the where_clause or OnConflictUpdate.
>
> This is legal:
> SELECT * FROM (SELECT true as update) f WHERE update;
> i.e. 'update' can be the last part of a WHERE clause.
>
> Essentially what you're trying to do with the nonassic is hiding that
> UPDATE and IGNORE need to be reserved keywords with the syntax you're
> proposing. We can either make them reserved or change the syntax.
>
> One way to avoid making them reserved keywords - which would be somewhat
> painful - is to add a 'DO' before the IGNORE/UPDATE. I.e. something like
>
> ON CONFLICT opt_conflict_expr DO OnConflictUpdateStmt
> | ON CONFLICT opt_conflict_expr DO IGNORE
>
> Greetings,
>
> Andres Freund
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>