Re: INSERT ... ON CONFLICT syntax issues

On Mon, Apr 27, 2015 at 7:21 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> 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.

What I proposed originally was ON DUPLICATE. Not ON CONFLICT. And I
still like that better. ON UNIQUE CONFLICT, which Andres mentioned,
gets us there too, but it's

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

There seem to be a few votes for NEW and OLD. That's what I proposed
originally, and (surprise, surprise) I still like that better too.

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

+1.

> * 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.

Seems fine.

> * 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).

I still think that constraints should never be named in the syntax.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company