On Mon, Sep 29, 2014 at 1:40 PM, Kevin Grittner <kgri...@ymail.com> wrote: > I think that the subset of the MERGE syntax that would be needed > for UPSERT behavior would be as follows. For one row as literals: > > MERGE INTO tab t > USING (VALUES ('foo', 'p1')) new(id, colB) > ON (t.id = new.id) > WHEN MATCHED THEN > UPDATE SET colB = new.colB > WHEN NOT MATCHED THEN > INSERT (id, colB) VALUES (new.id, new.colB); > > If you have a bunch of rows in a "bar" table you want to merge in: > > MERGE INTO tab t > USING (SELECT id, colB FROM bar) b > ON (t.id = b.id) > WHEN MATCHED THEN > UPDATE SET colB = b.colB > WHEN NOT MATCHED THEN > INSERT (id, colB) VALUES (b.id, b.colB); > > I fail to see how this is harder or more problematic than the > nonstandard suggestions that have been floated. I don't know why > we would be even *considering* a nonstandard syntax rather than > saying that only this subset is supported *so far*.
Heikki, Andres and I are against using MERGE for this, fwiw. Tom seemed to think so too, on previous occasions. It isn't a matter of alternative syntaxes. I have described in detail why I think it's a bad idea - I have linked to that about 3 times in this thread. It paints us into a corner when we go to make this do what MERGE is supposed to do. Do you want a feature that, when fully generalized, plays a special visibility game based on whether or not some exact set of conditions are met? That is a non-starter, IMV. The whole idea of using an arbitrary join syntax seems great, but I need something that works backwards from would-be unique violations. That's the only way to preserve the UPSERT guarantees (atomicity, definite insert or update). -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers