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 <simon(at)2ndquadrant(dot)com> 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(at)mail(dot)gmail(dot)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