Re: INSERT ... ON CONFLICT syntax issues

On 04/25/2015 12:01 PM, Andres Freund wrote:
> INSERT ... ON CONFLICT (cola, colb [WHERE predicate_for_partial]) UPDATE|IGNORE
>
> My problem with the WHERE being inside the parens in the above is that
> it's
> a) different from CREATE INDEX
> b) unclear whether the WHERE belongs to colb or the whole index
> expression. The equivalent for aggregates, which I bet is going to be
> used less often, caused a fair amount of confusing.
>
> That's why I wanted the WHERE outside the (), which requires either
> adding DO between the index inference clause, and the action, to avoid
> ambiguities in the grammar.

Yeah, having the WHERE outside the parens seems much nicer. What is the
ambiguity?

> But I'm generally having some doubts about the syntax.
>
> Right now it's
> INSERT ... ON CONFLICT opt_on_conf_clause UPDATE|IGNORE.
>
> A couple things:
>
> a) Why is is 'CONFLICT"? We're talking about a uniquness violation. What
> if we, at some later point, also want to handle other kind of
> violations? Shouldn't it be ON UNIQUE CONFLICT/ERROR/VIOLATION ...

As Peter said, it's also for exclusion constraints. Perhaps "ON
CONSTRAINT VIOLATION"? It doesn't apply to foreign key constraints,
though. I think "ON CONFLICT" is fine.

> b) For me there's a WITH before the index inference clause missing, to
> have it read in 'SQL' style.

Agreed. ON would sound more natural than WITH though:

INSERT INTO mytable ON CONFLICT ON (keycol) UPDATE ...

The ability to specify a constraint by name hasn't been implemented, but
that would read quite naturally as:

INSERT INTO mytable ON CONFLICT ON CONSTRAINT my_constraint UPDATE ...

- Heikki