Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6e8977f

Browse files
deanrasheedpull[bot]
authored andcommitted
Update MERGE docs to mention that ONLY is supported.
Commit7103ebb added support for MERGE, which included support forinheritance hierarchies, but didn't document the fact that ONLY couldbe specified before the source and/or target tables to exclude tablesinheriting from the tables specified.Update merge.sgml to mention this, and while at it, add someregression 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 parent4990578 commit6e8977f

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
ALTERTABLE measurement_y2007m01 DROP COLUMN filler;
12331233
ALTERTABLE measurement_y2007m01 INHERIT measurement;
1234+
INSERT INTO measurementVALUES (0,'2005-07-21',5,15);
12341235

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

12661267
CREATETABLEnew_measurement (LIKE measurement) WITH (autovacuum_enabled=off);
1268+
INSERT INTO new_measurementVALUES (0,'2005-07-21',25,20);
12671269
INSERT INTO new_measurementVALUES (1,'2006-03-01',20,10);
12681270
INSERT INTO new_measurementVALUES (1,'2006-02-16',50,10);
12691271
INSERT INTO new_measurementVALUES (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_measurementVALUES (1,'2007-01-15',5,NULL);
12731275
INSERT INTO new_measurementVALUES (1,'2007-01-16',10,10);
12741276

1277+
BEGIN;
1278+
MERGE INTO ONLY measurement m
1279+
USING new_measurement nmON
1280+
(m.city_id=nm.city_idandm.logdate=nm.logdate)
1281+
WHEN MATCHEDANDnm.peaktemp ISNULL THENDELETE
1282+
WHEN MATCHED THENUPDATE
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 measurementORDER BY city_id, logdate, peaktemp;
1290+
ROLLBACK;
1291+
12751292
MERGE into measurement m
12761293
USING new_measurement nmON
12771294
(m.city_id=nm.city_idandm.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 measurementORDER BY city_id, logdate;
1304+
1305+
BEGIN;
1306+
MERGE INTO new_measurement nm
1307+
USING ONLY measurement mON
1308+
(nm.city_id=m.city_idandnm.logdate=m.logdate)
1309+
WHEN MATCHED THENDELETE;
1310+
1311+
SELECT*FROM new_measurementORDER BY city_id, logdate;
1312+
ROLLBACK;
1313+
1314+
MERGE INTO new_measurement nm
1315+
USING measurement mON
1316+
(nm.city_id=m.city_idandnm.logdate=m.logdate)
1317+
WHEN MATCHED THENDELETE;
1318+
1319+
SELECT*FROM new_measurementORDER BY city_id, logdate;
1320+
12871321
DROPTABLE measurement, new_measurement CASCADE;
12881322
DROPFUNCTION measurement_insert_trigger();
12891323

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp