Horton

Re: [HACKERS] MERGE SQL Statement for PG11

Pavan Deolasee <pavan.deolasee@gmail.com>

View thread View on postgr.es
From: Pavan Deolasee <pavan.deolasee@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Robert Haas <robertmhaas@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Simon Riggs <simon@2ndquadrant.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Andrew Dunstan <andrew.dunstan@2ndquadrant.com>, Pavan Deolasee <pavan.deolasee@2ndquadrant.com>, Stephen Frost <sfrost@snowman.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2018-03-22T12:24:07Z
Lists: pgsql-hackers 
On Thu, Mar 22, 2018 at 1:15 AM, Peter Geoghegan <pg@bowt.ie> wrote:

>
> >
> >
> > No, it did not. We only support VALUES clause with INSERT action.
>
> But can't you have a subselect in the VALUES()? Support for subselects
> seems like a totally distinct thing to the restriction that only a
> (single row) VALUES() is allowed in INSERT actions.
>
>
Ah, right. That works even today.

postgres=# CREATE TABLE target (a int, b text);
CREATE TABLE

postgres=# MERGE INTO target USING (SELECT 1) s ON false WHEN NOT MATCHED
THEN INSERT VALUES ((SELECT count(*) FROM pg_class), (SELECT relname FROM
pg_class LIMIT 1));
MERGE 1

postgres=# SELECT * FROM target;
  a  |       b
-----+----------------
 755 | pgbench_source
(1 row)


Thanks,
Pavan


-- 
 Pavan Deolasee                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

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.