Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On 09/28/2014 11:31 PM, Peter Geoghegan wrote:
> On Sun, Sep 28, 2014 at 1:17 PM, Simon Riggs  wrote:
>> MERGE INTO tab USING VALUES ('foo')
>> WHEN NOT MATCHED THEN
>>   INSERT (colB)
>> WHEN MATCHED THEN
>>   UPDATE SET colB = NEW.p1
>>
>> and throwing "ERROR: full syntax for MERGE not implemented yet" if
>> people stretch too far.
>
> That isn't the MERGE syntax either. Where is the join?
>
> I've extensively discussed why I think we should avoid calling
> something upsert-like MERGE, as you know:
> http://www.postgresql.org/message-id/flat/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com#CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com
>
> We *should* have a MERGE feature, but one that serves the actual MERGE
> use-case well. That is an important use-case; it just isn't the one
> I'm interested in right now.

I agree we should not use the MERGE keyword for this. The upsert feature 
has tighter concurrency requirements than the SQL MERGE command, and 
that might come back to bite us. It would be highly confusing if some 
variants of MERGE are concurrency-safe and others are not, but if we now 
promise that our MERGE command is always concurrency-safe, that promise 
might be difficult to keep for the full MERGE syntax, and for whatever 
extensions the SQL committee comes up in the future.

That said, it would be handy if the syntax was closer to MERGE. Aside 
from the concurrency issues, it does the same thing, right? So how about 
making the syntax identical to MERGE, except for swapping the MERGE 
keyword with e.g. UPSERT?

- Heikki