From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Peter Geoghegan <pg(at)heroku(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | Andres Freund <andres(at)2ndquadrant(dot)com>, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Craig Ringer <craig(at)2ndquadrant(dot)com> |
Subject: | Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} |
Date: | 2014-09-29 20:40:35 |
Message-ID: | 1412023235.17282.YahooMailNeo@web122304.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> As I've mentioned, it isn't the MERGE syntax because that is
> quite a different thing. There is a place for it, but it's not
> strategically important in the same way as upsert is.
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*.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2014-09-29 20:41:45 | Re: open items for 9.4 |
Previous Message | Andrew Dunstan | 2014-09-29 20:38:57 | Re: json (b) and null fields |