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

On 29/09/14 09:31, 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.
>
> FWIW, I agree that it wouldn't be much work to do this - what you
> present here really is just a different syntax for what I have here
> (which isn't MERGE). I think it would be counter-productive to pursue
> this, though. Also, what about limiting the unique indexes under
> consideration?
>
> There was informal meeting of this at the dev meeting a in 2012.
>
How about have a stub page for MERGE, saying it is not implemented yet,
but how about considering UPSERT - or something of that nature?

I can suspect that people are much more likely to look for 'MERGE' in an
index, or look for 'MERGE' in the list of SQL commands, than 'UPSERT'.

Cheers,
Gavin