pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Nico Williams <nico@cryptonector.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: MERGE SQL Statement for PG11
Date: Thu, 2 Nov 2017 14:39:33 -0500
Message-ID: <20171102193932.GS4496@localhost> (raw)
In-Reply-To: <20171102191612.GR4496@localhost>
References: <CANP8+jJLvkBgaet6O3j-yGapu9kUSv69PTMQxGwYuq2Eb-t2dw@mail.gmail.com>
	<CAH2-Wzm0A0PUiAeMsmj6P+P4jaP+yz3dXH5f=xPWnuyX5m8ebQ@mail.gmail.com>
	<CANP8+j+Wij0NoewRaNA=U6pfz3CxV9AXdBrkvRmGcq+ZiBQxpQ@mail.gmail.com>
	<CAH2-Wz=4Nha_cMdwTVcZwD9Gt1uzXEfpKOv72vBhCAJNEGQ0kw@mail.gmail.com>
	<CANP8+jJBpB7e+sHs=gPaqL-E2Prtmv7fnLN9D34BO8_mJuy_KQ@mail.gmail.com>
	<CAH2-Wzk+4013Ht1d9uNef2YBPdkTyyhdH=CDQ527K4iCfatu+Q@mail.gmail.com>
	<CANP8+jKNXUjW4FT=1me5FzTpvMhwWkK4-Ea3F1_Vw9vE0rkt0A@mail.gmail.com>
	<20171101182032.GA7104@marmot>
	<CANP8+jJtsL3fYgiRWYB5vM9g98iFNHgBeCtPufUkgVShpEFvvg@mail.gmail.com>
	<20171102191612.GR4496@localhost>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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



view thread (265+ messages)  latest in thread

Message-ID: <20171102193932.GS4496@localhost>
Permalink:  ../20171102193932.GS4496@localhost/
Also on:    postgresql.org/message-id/20171102193932.GS4496@localhost

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: nico@cryptonector.com, simon@2ndquadrant.com, pg@bowt.ie, robertmhaas@gmail.com, michael.paquier@gmail.com
  Subject: Re: MERGE SQL Statement for PG11
  In-Reply-To: <20171102193932.GS4496@localhost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox