Re: INSERT ... ON CONFLICT syntax issues

On 26/04/15 12:08, Andres Freund wrote:
> 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:
>>>
>>> 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.
>

The DO variant with WHERE outside of parenthesis sounds fine to me. Or
at least better than the alternatives I've seen or can come up with.

>>> 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 ;)
>

The ON CONFLICT ON sounds really weird to me. Either ON CONSTRAINT
VIOLATION (foo) or ON CONFLICT [WITH] (foo) both seem acceptable.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services