8000 Update MERGE docs to mention that ONLY is supported. · postgrespro/postgres@5defdef · GitHub
[go: up one dir, main page]
Skip to content

    • Commit 5defdef

    Browse files
    deanrasheeddeanrasheed
    committed
    Update MERGE docs to mention that ONLY is supported.
    Commit 7103ebb added support for MERGE, which included support for inheritance hierarchies, but didn't document the fact that ONLY could be specified before the source and/or target tables to exclude tables inheriting from the tables specified. Update merge.sgml to mention this, and while at it, add some regression tests to cover it. Dean Rasheed, reviewed by Nathan Bossart. Backpatch to 15, where MERGE was added. Discussion: https://postgr.es/m/CAEZATCU0XM-bJCvpJuVRU3UYNRqEBS6g4-zH%3Dj9Ye0caX8F6uQ%40mail.gmail.com
    1 parent 07c29ca commit 5defdef

    File tree

    3 files changed

    +115
    -4
    lines changed

    3 files changed

    +115
    -4
    lines changed

    ‎doc/src/sgml/ref/merge.sgml

    Lines changed: 16 additions & 3 deletions
    Original file line numberDiff line numberDiff line change
    @@ -22,13 +22,13 @@ PostgreSQL documentation
    2222
    <refsynopsisdiv>
    2323
    <synopsis>
    2424
    [ WITH <replaceable class="parameter">with_query</replaceable> [, ...] ]
    25-
    MERGE INTO <replaceable class="parameter">target_table_name</replaceable> [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ]
    25+
    MERGE INTO [ ONLY ] <replaceable class="parameter">target_table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ]
    2626
    USING <replaceable class="parameter">data_source</replaceable> ON <replaceable class="parameter">join_condition</replaceable>
    2727
    <replaceable class="parameter">when_clause</replaceable> [...]
    2828

    2929
    <phrase>where <replaceable class="parameter">data_source</replaceable> is:</phrase>
    3030

    31-
    { <replaceable class="parameter">source_table_name</replaceable> | ( <replaceable class="parameter">source_query</replaceable> ) } [ [ AS ] <replaceable class="parameter">source_alias</replaceable> ]
    31+
    { [ ONLY ] <replaceable class="parameter">source_table_name</replaceable> [ * ] | ( <replaceable class="parameter">source_query</replaceable> ) } [ [ AS ] <replaceable class="parameter">source_alias</replaceable> ]
    3232

    3333
    <phrase>and <replaceable class="parameter">when_clause</replaceable> is:</phrase>
    3434

    @@ -129,6 +129,14 @@ DELETE
    129129
    <listitem>
    130130
    <para>
    131131
    The name (optionally schema-qualified) of the target table to merge into.
    132+
    If <literal>ONLY</literal> is specified before the table name, matching
    133+
    rows are updated or deleted in the named table only. If
    134+
    <literal>ONLY</literal> is not specified, matching rows are also updated
    135+
    or deleted in any tables inheriting from the named table. Optionally,
    136+
    <literal>*</literal> can be specified after the table name to explicitly
    137+
    indicate that descendant tables are included. The
    138+
    <literal>ONLY</literal> keyword and <literal>*</literal> option do not
    139+
    affect insert actions, which always insert into the named table only.
    132140
    </para>
    133141
    </listitem>
    134142
    </varlistentry>
    @@ -151,7 +159,12 @@ DELETE
    151159
    <listitem>
    152160
    <para>
    153161
    The name (optionally schema-qualified) of the source table, view, or
    154-
    transition table.
    162+
    transition table. If <literal>ONLY</literal> is specified before the
    163+
    table name, matching rows are included from the named table only. If
    164+
    <literal>ONLY</literal> is not specified, matching rows are also included
    165+
    from any tables inheriting from the named table. Optionally,
    166+
    <literal>*</literal> can be specified after the table name to explicitly
    167+
    indicate that descendant tables are included.
    155168
    </para>
    156169
    </listitem>
    157170
    </varlistentry>

    ‎src/test/regress/expected/merge.out

    Lines changed: 65 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -1886,6 +1886,7 @@ CREATE TABLE measurement_y2007m01 (
    18861886
    ) WITH (autovacuum_enabled=off);
    18871887
    ALTER TABLE measurement_y2007m01 DROP COLUMN filler;
    18881888
    ALTER TABLE measurement_y2007m01 INHERIT measurement;
    1889+
    INSERT INTO measurement VALUES (0, '2005-07-21', 5, 15);
    18891890
    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
    18901891
    RETURNS TRIGGER AS $$
    18911892
    BEGIN
    @@ -1917,22 +1918,55 @@ INSERT INTO measurement VALUES (1, '2007-01-17', 10, 10);
    19171918
    SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
    19181919
    tableoid | city_id | logdate | peaktemp | unitsales
    19191920
    ----------------------+---------+------------+----------+-----------
    1921+
    measurement | 0 | 07-21-2005 | 5 | 15
    19201922
    measurement_y2006m02 | 1 | 02-10-2006 | 35 | 10
    19211923
    measurement_y2006m02 | 1 | 02-16-2006 | 45 | 20
    19221924
    measurement_y2006m03 | 1 | 03-17-2006 | 25 | 10
    19231925
    measurement_y2006m03 | 1 | 03-27-2006 | 15 | 40
    19241926
    measurement_y2007m01 | 1 | 01-15-2007 | 10 | 10
    19251927
    measurement_y2007m01 | 1 | 01-17-2007 | 10 | 10
    1926-
    (6 rows)
    1928+
    (7 rows)
    19271929

    19281930
    CREATE TABLE new_measurement (LIKE measurement) WITH (autovacuum_enabled=off);
    1931+
    INSERT INTO new_measurement VALUES (0, '2005-07-21', 25, 20);
    19291932
    INSERT INTO new_measurement VALUES (1, '2006-03-01', 20, 10);
    19301933
    INSERT INTO new_measurement VALUES (1, '2006-02-16', 50, 10);
    19311934
    INSERT INTO new_measurement VALUES (2, '2006-02-10', 20, 20);
    19321935
    INSERT INTO new_measurement VALUES (1, '2006-03-27', NULL, NULL);
    19331936
    INSERT INTO new_measurement VALUES (1, '2007-01-17', NULL, NULL);
    19341937
    INSERT INTO new_measurement VALUES (1, '2007-01-15', 5, NULL);
    19351938
    INSERT INTO new_measurement VALUES (1, '2007-01-16', 10, 10);
    1939+
    BEGIN;
    1940+
    MERGE INTO ONLY measurement m
    1941+
    USING new_measurement nm ON
    1942+
    (m.city_id = nm.city_id and m.logdate=nm.logdate)
    1943+
    WHEN MATCHED AND nm.peaktemp IS NULL THEN DELETE
    1944+
    WHEN MATCHED THEN UPDATE
    1945+
    SET peaktemp = greatest(m.peaktemp, nm.peaktemp),
    1946+
    unitsales = m.unitsales + coalesce(nm.unitsales, 0)
    1947+
    WHEN NOT MATCHED THEN INSERT
    1948+
    (city_id, logdate, peaktemp, unitsales)
    1949+
    VALUES (city_id, logdate, peaktemp, unitsales);
    1950+
    SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate, peaktemp;
    1951+
    tableoid | city_id | logdate | peaktemp | unitsales
    1952+
    ----------------------+---------+------------+----------+-----------
    1953+
    measurement | 0 | 07-21-2005 | 25 | 35
    1954+
    measurement_y2006m02 | 1 | 02-10-2006 | 35 | 10
    1955+
    measurement_y2006m02 | 1 | 02-16-2006 | 45 | 20
    1956+
    measurement_y2006m02 | 1 | 02-16-2006 | 50 | 10
    1957+
    measurement_y2006m03 | 1 | 03-01-2006 | 20 | 10
    1958+
    measurement_y2006m03 | 1 | 03-17-2006 | 25 | 10
    1959+
    measurement_y2006m03 | 1 | 03-27-2006 | 15 | 40
    1960+
    measurement_y2006m03 | 1 | 03-27-2006 | |
    1961+
    measurement_y2007m01 | 1 | 01-15-2007 | 5 |
    1962+
    measurement_y2007m01 | 1 | 01-15-2007 | 10 | 10
    1963+
    measurement_y2007m01 | 1 | 01-16-2007 | 10 | 10
    1964+
    measurement_y2007m01 | 1 | 01-17-2007 | 10 | 10
    1965+
    measurement_y2007m01 | 1 | 01-17-2007 | |
    1966+
    measurement_y2006m02 | 2 | 02-10-2006 | 20 | 20
    1967+
    (14 rows)
    1968+
    1969+
    ROLLBACK;
    19361970
    MERGE into measurement m
    19371971
    USING new_measurement nm ON
    19381972
    (m.city_id = nm.city_id and m.logdate=nm.logdate)
    @@ -1946,15 +1980,45 @@ WHEN NOT MATCHED THEN INSERT
    19461980
    SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
    19471981
    tableoid | city_id | logdate | peaktemp | unitsales
    19481982
    ----------------------+---------+------------+----------+-----------
    1983+
    measurement | 0 | 07-21-2005 | 25 | 35
    19491984
    measurement_y2006m02 | 1 | 02-10-2006 | 35 | 10
    19501985
    measurement_y2006m02 | 1 | 02-16-2006 | 50 | 30
    19511986
    measurement_y2006m03 | 1 | 03-01-2006 | 20 | 10
    19521987
    measurement_y2006m03 | 1 | 03-17-2006 | 25 | 10
    19531988
    measurement_y2007m01 | 1 | 01-15-2007 | 10 | 10
    19541989
    measurement_y2007m01 | 1 | 01-16-2007 | 10 | 10
    19551990
    measurement_y2006m02 | 2 | 02-10-2006 | 20 | 20
    1991+
    (8 rows)
    1992+
    1993+
    BEGIN;
    1994+
    MERGE INTO new_measurement nm
    1995+
    USING ONLY measurement m ON
    1996+
    (nm.city_id = m.city_id and nm.logdate=m.logdate)
    1997+
    WHEN MATCHED THEN DELETE;
    1998+
    SELECT * FROM new_measurement ORDER BY city_id, logdate;
    1999+
    city_id | logdate | peaktemp | unitsales
    2000+
    ---------+------------+----------+-----------
    2001+
    1 | 02-16-2006 | 50 | 10
    2002+
    1 | 03-01-2006 | 20 | 10
    2003+
    1 | 03-27-2006 | |
    2004+
    1 | 01-15-2007 | 5 |
    2005+
    1 | 01-16-2007 | 10 | 10
    2006+
    1 | 01-17-2007 | |
    2007+
    2 | 02-10-2006 | 20 | 20
    19562008
    (7 rows)
    19572009

    2010+
    ROLLBACK;
    2011+
    MERGE INTO new_measurement nm
    2012+
    USING measurement m ON
    2013+
    (nm.city_id = m.city_id and nm.logdate=m.logdate)
    2014+
    WHEN MATCHED THEN DELETE;
    2015+
    SELECT * FROM new_measurement ORDER BY city_id, logdate;
    2016+
    city_id | logdate | peaktemp | unitsales
    2017+
    ---------+------------+----------+-----------
    2018+
    1 | 03-27-2006 | |
    2019+
    1 | 01-17-2007 | |
    2020+
    (2 rows)
    2021+
    19582022
    DROP TABLE measurement, new_measurement CASCADE;
    19592023
    NOTICE: drop cascades to 3 other objects
    19602024
    DETAIL: drop cascades to table measurement_y2006m02

    ‎src/test/regress/sql/merge.sql

    Lines changed: 34 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1231,6 +1231,7 @@ CREATE TABLE measurement_y2007m01 (
    12311231
    ) WITH (autovacuum_enabled=off);
    12321232
    ALTER TABLE measurement_y2007m01 DROP COLUMN filler;
    12331233
    ALTER TABLE measurement_y2007m01 INHERIT measurement;
    1234+
    INSERT INTO measurement VALUES (0, '2005-07-21', 5, 15);
    12341235

    12351236
    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
    12361237
    RETURNS TRIGGER AS $$
    @@ -1264,6 +1265,7 @@ INSERT INTO measurement VALUES (1, '2007-01-17', 10, 10);
    12641265
    SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
    12651266

    12661267
    CREATE TABLE new_measurement (LIKE measurement) WITH (autovacuum_enabled=off);
    1268+
    INSERT INTO new_measurement VALUES (0, '2005-07-21', 25, 20);
    12671269
    INSERT INTO new_measurement VALUES (1, '2006-03-01', 20, 10);
    12681270
    INSERT INTO new_measurement VALUES (1, '2006-02-16', 50, 10);
    12691271
    INSERT INTO new_measurement VALUES (2, '2006-02-10', 20, 20);
    @@ -1272,6 +1274,21 @@ INSERT INTO new_measurement VALUES (1, '2007-01-17', NULL, NULL);
    12721274
    INSERT INTO new_measurement VALUES (1, '2007-01-15', 5, NULL);
    12731275
    INSERT INTO new_measurement VALUES (1, '2007-01-16', 10, 10);
    12741276

    1277+
    BEGIN;
    1278+
    MERGE INTO ONLY measurement m
    1279+
    USING new_measurement nm ON
    1280+
    (m.city_id = nm.city_id and m.logdate=nm.logdate)
    1281+
    WHEN MATCHED AND nm.peaktemp IS NULL THEN DELETE
    1282+
    WHEN MATCHED THEN UPDATE
    1283+
    SET peaktemp = greatest(m.peaktemp, nm.peaktemp),
    1284+
    unitsales = m.unitsales + coalesce(nm.unitsales, 0)
    1285+
    WHEN NOT MATCHED THEN INSERT
    1286+
    (city_id, logdate, peaktemp, unitsales)
    1287+
    VALUES (city_id, logdate, peaktemp, unitsales);
    1288+
    1289+
    SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate, peaktemp;
    1290+
    ROLLBACK;
    1291+
    12751292
    MERGE into measurement m
    12761293
    USING new_measurement nm ON
    12771294
    (m.city_id = nm.city_id and m.logdate=nm.logdate)
    @@ -1284,6 +1301,23 @@ WHEN NOT MATCHED THEN INSERT
    12841301
    VALUES (city_id, logdate, peaktemp, unitsales);
    12851302

    12861303
    SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
    1304+
    1305+
    BEGIN;
    1306+
    MERGE INTO new_measurement nm
    1307+
    USING ONLY measurement m ON
    1308+
    (nm.city_id = m.city_id and nm.logdate=m.logdate)
    1309+
    WHEN MATCHED THEN DELETE;
    1310+
    1311+
    SELECT * FROM new_measurement ORDER BY city_id, logdate;
    1312+
    ROLLBACK;
    1313+
    1314+
    MERGE INTO new_measurement nm
    1315+
    USING measurement m ON
    1316+
    (nm.city_id = m.city_id and nm.logdate=m.logdate)
    1317+
    WHEN MATCHED THEN DELETE;
    1318+
    1319+
    SELECT * FROM new_measurement ORDER BY city_id, logdate;
    1320+
    12871321
    DROP TABLE measurement, new_measurement CASCADE;
    12881322
    DROP FUNCTION measurement_insert_trigger();
    12891323

    0 commit comments

    Comments
     (0)
    0