On 28 September 2014 21:31, Peter Geoghegan <pg@heroku.com> wrote:
> On Sun, Sep 28, 2014 at 1:17 PM, Simon Riggs <simon@2ndquadrant.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.
> 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.
>
> 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.
I agreed with the initial proposition to go for a different syntax.
Now that I see the new syntax, I have changed my mind. The new syntax
is much worse, I am sorry to say.
MERGE standard does not offer guidance on concurrent effects, but
there is no confusion as to how it works. We can impose our own
concurrency rules since those are not covered by the standard. These
are quite clear for single row inputs anyway, i.e. a VALUES clause.
>
> That isn't the MERGE syntax either. Where is the join?
>
There doesn't need to be one. INSERT assumes that if a column list is
not mentioned then the VALUES clause is joined directly to the table,
so we can do the same thing for MERGE.
-- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
