Re: INSERT ... ON CONFLICT syntax issues

On Mon, Apr 27, 2015 at 10:20 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Agreed, and I like the DO [ UPDATE | NOTHING ] too.

Here is what I think I need to do:

* Don't change the ON CONFLICT spelling.

* Don't change the names of the pseudo-alias EXCLUDED.* (or the alias
TARGET.*). Those seem fine to me as well.

* Change the syntax to put the WHERE clause used to infer partial
indexes outside parens.

* Change the syntax to make this work, by adding the fully reserved
keyword DO. Assuming you have a partial index (WHERE is_active) on the
column "key", you're left with:

INSERT .... ON CONFLICT (key) where is_active DO UPDATE set ... WHERE ... ;

or:

INSERT .... ON CONFLICT (key) where is_active DO NOTHING;

The DO keyword makes this work where it cannot otherwise, because it's
a RESERVED_KEYWORD.

* Finally, add "ON CONFLICT ON CONSTRAINT my_constraint" support, so
you can name (exactly one) constraint by name. Particularly useful for
unique constraints. I really don't want to make this accept multiple
constraints, even though we may infer multiple constraints, because
messy, and is probably too complex to every be put to good use
(bearing in mind that exclusion constraints, that really need this,
will still only be supported by the IGNORE/DO NOTHING variant).

Are we in agreement?
--
Peter Geoghegan