Re: INSERT ... ON CONFLICT syntax issues

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

I've pushed code that does all this to Github. Documentation changes
will follow soon.

> * 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 have yet to do this, but it should be fairly straightforward.

--
Peter Geoghegan