Re: INSERT ... ON CONFLICT syntax issues

On April 26, 2015 11:22:01 AM GMT+02:00, 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?

With a full keyword in between (like DO), there's none. But without it its ambiguous where a trailing UPDATE belongs to. At least from the point of a LALR grammar. WHERE UPDATE; is legal. I don't see the DO as much of a problem though.

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

What if we, as at least I have previously wished for, want to allow handling other types of constraints? It'd be quite cool to be able to insert the referenced key on a fkey violation for some use cases.

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

I chose WITh because of the repeated DO; that's all ;)

---
Please excuse brevity and formatting - I am writing this on my mobile phone.