Re: INSERT ... ON CONFLICT syntax issues

* Heikki Linnakangas (hlinnaka(at)iki(dot)fi) wrote:
> 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?

I like having it outside the parens also.

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

I don't mind using "CONFLICT" here, seems to make sense to me.

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

I don't particularly like the double-ON in this..

I've not tried, but is the first ON required to be a full keyword?
Seems like it probably is, but just to finish the thought I had, what
about:

INSERT INTO mytable .. IF CONFLICT ON (a,b) WHERE .. THEN UPDATE

IF is currently just an unreserved keyword though.

We could use FOR though:

INSERT INTO mytable .. FOR CONFLICT ON (a,b) WHERE .. THEN UPDATE

Though that'd probably sound better as:

INSERT INTO mytable .. FOR CONFLICT ON (a,b) WHERE .. DO UPDATE

Another option is:

INSERT INTO mytable .. WHEN CONFLICT ON (a,b) WHERE .. DO UPDATE

Which could also be:

INSERT INTO mytable .. WHEN CONFLICT ON (a,b) WHERE .. THEN UPDATE

of course..

What's important, in my view, is to keep the simple case simple and so
I'm not particularly wedded to any of these approaches, just trying to
help with other suggestions.

INSERT INTO mytable VALUES ('key1','key2','val1','val2')
ON CONFLICT UPDATE SET val1 = 'val1', val2 = 'val2';

strikes me as a the 99% use-case here that we need to keep sane, and
it'd be really nice if we didn't have to include the SET clause and
duplicate those values at all.. That could be something we add later
though, I don't think it needs to be done now.

Thanks!

Stephen