Horton

Re: MERGE SQL Statement for PG11

Nico Williams <nico@cryptonector.com>

View thread View on postgr.es
From: Nico Williams <nico@cryptonector.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Peter Geoghegan <pg@bowt.ie>, Robert Haas <robertmhaas@gmail.com>, Michael Paquier <michael.paquier@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2017-11-02T19:39:33Z
Lists: pgsql-hackers 
If nothing else, anyone needing MERGE can port their MERGE statements to
a DML with DML-containing CTEs...

The generic mapping would be something like this, I think:

WITH
    rows AS (SELECT <target> FROM <target> WHERE <condition>)
  , updated AS (
    UPDATE <target>
    SET ...
    WHERE <key> IN (SELECT <key> FROM rows) /* matched */
    RETURNING <target>
  )
  , inserted AS (
    INSERT INTO <target>
    SELECT ...
    WHERE <key> NOT IN (SELECT <key> FROM rows) /* not matched */
    RETURNING <target>
  )
DELETE FROM <target>
WHERE (...) AND
  <key> NOT IN (SELECT <key> FROM updated UNION
                SELECT <key> FROM inserted);

Nico
--

Commits

  1. Add support for MERGE SQL command

  2. Add API of sorts for transition table handling in trigger.c

  3. Revert MERGE patch

  4. Fix several bugs related to ON CONFLICT's EXCLUDED pseudo relation.