Fork me on GitHub

daydayup863

人生就像一杯茶，不会苦一辈子，但总会苦一阵子。

0%

PostgreSQL 15 preview - 支持MERGE语法

置顶 | 发表于 分类于 ， ， ， 阅读次数： Valine：

早上上班途中看到PostgreSQL提交日志，已经支持MERGE语法，现在就上手体验一把。

语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Command:     MERGE
Description: conditionally insert, update, or delete rows of a table
Syntax:
[ WITH with_query [, ...] ]
MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]
where data_source is
{ source_table_name | ( source_query ) } [ [ AS ] source_alias ]
and when_clause is
{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }
and merge_insert is
INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }
and merge_update is
UPDATE SET { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
and merge_delete is
DELETE
URL: https://www.postgresql.org/docs/devel/sql-merge.html

可以看到，支持与WITH组合使用，merge_insert/merge_update/merge_delete 后不需要跟表名.

测试准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mydb=# CREATE TABLE target (tid integer, balance integer);
CREATE TABLE
mydb=# CREATE TABLE source (sid integer, delta integer); --no index
CREATE TABLE
mydb=# INSERT INTO target VALUES (1, 10);
INSERT 0 1
mydb=# INSERT INTO target VALUES (2, 20);
INSERT 0 1
mydb=# INSERT INTO target VALUES (3, 30);
INSERT 0 1
mydb=#
mydb=# select * from target ;
 tid | balance
-----+---------
   1 |      10
   2 |      20
   3 |      30
(3 rows)
mydb=# select * from source;
 sid | delta
-----+-------
(0 rows)

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
mydb=# INSERT INTO source VALUES (4, 40);
INSERT 0 1
mydb=# INSERT INTO source VALUES (3, 20);
INSERT 0 1
mydb=# INSERT INTO source VALUES (2, 5);
INSERT 0 1
mydb=# select * from target order by tid;
 tid | balance
-----+---------
   1 |      10
   2 |      20
   3 |      30
(3 rows)
mydb=# select * from source order by sid;
 sid | delta
-----+-------
   2 |     5
   3 |    20
   4 |    40
(3 rows)
mydb=#
mydb=# BEGIN;
BEGIN
mydb=*# MERGE INTO target t
mydb-*# USING source AS s
mydb-*# ON t.tid = s.sid
mydb-*# WHEN MATCHED THEN
mydb-*# UPDATE SET balance = 0;
MERGE 2
mydb=*# SELECT * FROM target ORDER BY tid;
 tid | balance
-----+---------
   1 |      10
   2 |       0
   3 |       0
(3 rows)
mydb=*# ROLLBACK;
ROLLBACK
mydb=#
mydb=# BEGIN;
BEGIN
mydb=*# MERGE INTO target t
mydb-*# USING source AS s
mydb-*# ON t.tid = s.sid
mydb-*# WHEN MATCHED THEN
mydb-*# DELETE;
MERGE 2
mydb=*# SELECT * FROM target ORDER BY tid;
 tid | balance
-----+---------
   1 |      10
(1 row)
mydb=*# ROLLBACK;
ROLLBACK
mydb=#
mydb=#
mydb=#  BEGIN;
BEGIN
mydb=*#  MERGE INTO target t
mydb-*#  USING source AS s
mydb-*#  ON t.tid = s.sid
mydb-*#  WHEN MATCHED THEN
mydb-*#  DO NOTHING;
MERGE 2
mydb=*#  SELECT * FROM target ORDER BY tid;
 tid | balance
-----+---------
   1 |      10
   2 |      20
   3 |      30
(3 rows)
mydb=*#  ROLLBACK;
ROLLBACK
mydb=# ALTER TABLE target ADD PRIMARY KEY (tid);
ALTER TABLE
mydb=# INSERT INTO source VALUES (2, 5);
INSERT 0 1
mydb=# SELECT * FROM source ORDER BY sid;
 sid | delta
-----+-------
   2 |     5
   2 |     5
   3 |    20
   4 |    40
(4 rows)
mydb=# SELECT * FROM target ORDER BY tid;
 tid | balance
-----+---------
   1 |      10
   2 |      20
   3 |      30
(3 rows)
mydb=# BEGIN;
BEGIN
mydb=*# MERGE INTO target t
mydb-*# USING source AS s
mydb-*# ON t.tid = s.sid
mydb-*# WHEN MATCHED THEN
mydb-*# UPDATE SET balance = 0;
ERROR:  MERGE command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.
mydb=!# ROLLBACK;
ROLLBACK
mydb=#
mydb=# BEGIN;
BEGIN
mydb=*# MERGE INTO target t
mydb-*# USING source AS s
mydb-*# ON t.tid = s.sid
mydb-*# WHEN MATCHED THEN
mydb-*# UPDATE SET balance = t.balance + s.delta;
MERGE 2
mydb=*# SELECT * FROM target ORDER BY tid;
 tid | balance
-----+---------
   1 |      10
   2 |      25
   3 |      50
(3 rows)
mydb=*# ROLLBACK;
ROLLBACK
mydb=# BEGIN;
BEGIN
mydb=*# MERGE INTO target t　　　　　　　/* unreachable WHEN clause*/
mydb-*# USING source AS s
mydb-*# ON t.tid = s.sid
mydb-*# WHEN MATCHED THEN
mydb-*# DELETE
mydb-*# WHEN MATCHED AND s.delta > 0 THEN
mydb-*# UPDATE SET balance = t.balance - s.delta;
ERROR:  unreachable WHEN clause specified after unconditional WHEN clause
mydb=!# ROLLBACK;
ROLLBACK

限制

target与source不可为同一个表

1
2
3
4
5
6
7
mydb=# MERGE INTO target
mydb-# USING target
mydb-# ON tid = tid
mydb-# WHEN MATCHED THEN DO NOTHING;
ERROR:  name "target" specified more than once
DETAIL:  The name is used both as MERGE target table and data source.
mydb=#

不支持视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mydb=#  CREATE VIEW tv AS SELECT * FROM target;
CREATE VIEW
mydb=# select * from tv;
 tid | balance
-----+---------
   1 |      10
   2 |      20
   3 |      30
(3 rows)
mydb=# MERGE INTO tv t
mydb-# USING source s
mydb-# ON t.tid = s.sid
mydb-# WHEN NOT MATCHED THEN
mydb-# INSERT DEFAULT VALUES;
ERROR:  cannot execute MERGE on relation "tv"
DETAIL:  This operation is not supported for views.
mydb=#
mydb=# DROP VIEW tv;
DROP VIEW

不支持物化视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mydb=# CREATE MATERIALIZED VIEW mv AS SELECT * FROM target;
SELECT 3
mydb=# select * from mv;
 tid | balance
-----+---------
   1 |      10
   2 |      20
   3 |      30
(3 rows)
mydb=# MERGE INTO mv t
mydb-# USING source s
mydb-# ON t.tid = s.sid
mydb-# WHEN NOT MATCHED THEN
mydb-# INSERT DEFAULT VALUES;
ERROR:  cannot execute MERGE on relation "mv"
DETAIL:  This operation is not supported for materialized views.
mydb=#
mydb=# DROP MATERIALIZED VIEW mv;
DROP MATERIALIZED VIEW

target表不支持foreign table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
mydb=# show port;
 port
------
 5557
(1 row)
mydb=# create server s1 foreign data wrapper postgres_fdw OPTIONS (host 'localhost', port '5557', dbname 'mydb');
CREATE SERVER
mydb=#
mydb=# select user;
   user
----------
 postgres
(1 row)
mydb=# create user mapping for postgres SERVER s1 OPTIONS ( user 'postgres' );
CREATE USER MAPPING
mydb=#
mydb=#
mydb=# create foreign table target_ft (tid int, balance int) server  s1 options (schema_name 'public', table_name 'target');
CREATE FOREIGN TABLE
mydb=# create foreign table source_ft (sid int, delta int) server  s1 options (schema_name 'public', table_name 'source');
CREATE FOREIGN TABLE
mydb=# select * from target_ft;
 tid | balance
-----+---------
   1 |      10
   2 |      20
   3 |      30
(3 rows)
mydb=# select * from source_ft;
 sid | delta
-----+-------
(0 rows)
mydb=# begin; MERGE INTO target t
USING source_ft s
ON t.tid = s.sid
WHEN NOT MATCHED THEN
INSERT DEFAULT VALUES;
BEGIN
MERGE 0
mydb=*# rollback ;
ROLLBACK
mydb=#
mydb=#
mydb=# begin; MERGE INTO target_ft t
USING source s
ON t.tid = s.sid
WHEN NOT MATCHED THEN
INSERT DEFAULT VALUES;
BEGIN
ERROR:  cannot execute MERGE on relation "target_ft"
DETAIL:  This operation is not supported for foreign tables.
mydb=!#
mydb=!# rollback ;
ROLLBACK

不支持returning

提交日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
commit 7103ebb7aae8ab8076b7e85f335ceb8fe799097c
Author: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date:   Mon Mar 28 16:45:58 2022 +0200
    Add support for MERGE SQL command
    MERGE performs actions that modify rows in the target table using a
    source table or query. MERGE provides a single SQL statement that can
    conditionally INSERT/UPDATE/DELETE rows -- a task that would otherwise
    require multiple PL statements.  For example,
    MERGE INTO target AS t
    USING source AS s
    ON t.tid = s.sid
    WHEN MATCHED AND t.balance > s.delta THEN
      UPDATE SET balance = t.balance - s.delta
    WHEN MATCHED THEN
      DELETE
    WHEN NOT MATCHED AND s.delta > 0 THEN
      INSERT VALUES (s.sid, s.delta)
    WHEN NOT MATCHED THEN
      DO NOTHING;
    MERGE works with regular tables, partitioned tables and inheritance
    hierarchies, including column and row security enforcement, as well as
    support for row and statement triggers and transition tables therein.
    MERGE is optimized for OLTP and is parameterizable, though also useful
    for large scale ETL/ELT. MERGE is not intended to be used in preference
    to existing single SQL commands for INSERT, UPDATE or DELETE since there
    is some overhead.  MERGE can be used from PL/pgSQL.
    MERGE does not support targetting updatable views or foreign tables, and
    RETURNING clauses are not allowed either.  These limitations are likely
    fixable with sufficient effort.  Rewrite rules are also not supported,
    but it's not clear that we'd want to support them.
    Author: Pavan Deolasee <pavan.deolasee@gmail.com>
    Author: Álvaro Herrera <alvherre@alvh.no-ip.org>
    Author: Amit Langote <amitlangote09@gmail.com>
    Author: Simon Riggs <simon.riggs@enterprisedb.com>
    Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
    Reviewed-by: Andres Freund <andres@anarazel.de> (earlier versions)
    Reviewed-by: Peter Geoghegan <pg@bowt.ie> (earlier versions)
    Reviewed-by: Robert Haas <robertmhaas@gmail.com> (earlier versions)
    Reviewed-by: Japin Li <japinli@hotmail.com>
    Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
    Reviewed-by: Tomas Vondra <tomas.vondra@enterprisedb.com>
    Reviewed-by: Zhihong Yu <zyu@yugabyte.com>
    Discussion: https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com
    Discussion: https://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com
    Discussion: https://postgr.es/m/20201231134736.GA25392@alvherre.pgsql
-------------本文结束感谢您的阅读-------------
听说，打赏我的人都找到了真爱

欢迎关注我的其它发布渠道