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

Peter Geoghegan <pg@heroku.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