Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-hackers-owner+M338937=pgsql-hackers-arkari@postgresql.org>)
	id 1eALMm-0005mi-E7
	for pgsql-hackers@arkaria.postgresql.org; Thu, 02 Nov 2017 19:41:28 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-hackers-owner+M338937=pgsql-hackers-arkari@postgresql.org>)
	id 1eALMl-0005k5-QQ
	for pgsql-hackers@arkaria.postgresql.org; Thu, 02 Nov 2017 19:41:27 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <nico@cryptonector.com>)
	id 1eALL7-0002f3-9L
	for pgsql-hackers@postgresql.org; Thu, 02 Nov 2017 19:39:45 +0000
Received: from sub4.mail.dreamhost.com ([69.163.253.135]
 helo=homiemail-a55.g.dreamhost.com)
	by magus.postgresql.org with esmtps (TLS1.1:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.84_2)
	(envelope-from <nico@cryptonector.com>)
	id 1eALL3-0000MU-QP
	for pgsql-hackers@postgresql.org; Thu, 02 Nov 2017 19:39:44 +0000
Received: from homiemail-a55.g.dreamhost.com (localhost [127.0.0.1])
	by homiemail-a55.g.dreamhost.com (Postfix) with ESMTP id 003E068003C1B;
	Thu,  2 Nov 2017 12:39:38 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha1; c=relaxed; d=cryptonector.com; h=date
	:from:to:cc:subject:message-id:references:mime-version
	:content-type:in-reply-to; s=cryptonector.com; bh=jjc2AZckWqsCfI
	5rsBxZ7kOCH2I=; b=lMSwcGTlkOZY33SzvlZl0uJmXaG89g81rcMf2VowAwD8Gz
	YnDnq71C/5XK6AAzPy5jxtMmFWjUPLXnYUqWlTU/mv3zOo0rvzkTY6F+T8SmbcP4
	+7AdLSM1dR69TYXfdwTlpu8Vzep/mPbZmkUHcxaGA0FfPZa4XLwQEYd844W74=
Received: from localhost (cpe-70-123-158-140.austin.res.rr.com
 [70.123.158.140])
	(using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits))
	(No client certificate requested)
	(Authenticated sender: nico@cryptonector.com)
	by homiemail-a55.g.dreamhost.com (Postfix) with ESMTPSA id 87CD168003C0F;
	Thu,  2 Nov 2017 12:39:37 -0700 (PDT)
Date: Thu, 2 Nov 2017 14:39:33 -0500
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>
Subject: Re: MERGE SQL Statement for PG11
Message-ID: <20171102193932.GS4496@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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20171102191612.GR4496@localhost>
User-Agent: Mutt/1.5.24 (2015-08-30)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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