Re: [HACKERS] MERGE SQL Statement for PG11 - Mailing list pgsql-hackers

From Nico Williams
Subject Re: [HACKERS] MERGE SQL Statement for PG11
Date
Msg-id 20171102193932.GS4496@localhost
Whole thread Raw
In response to Re: [HACKERS] MERGE SQL Statement for PG11  (Simon Riggs <simon@2ndquadrant.com>)
List 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
-- 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: [HACKERS] Re: PANIC: invalid index offnum: 186 when processing BRIN indexes in VACUUM
Next
From: Peter Geoghegan
Date:
Subject: Re: [HACKERS] MERGE SQL Statement for PG11