在SQL Server以及Oracle里经常能看到Merge语句，PG 15现在也支持Merge语句。
下面我们使用PG官方每天自动更新的快照版本来测试Merge语句的用法。
快照版本下载地址
https://www.postgresql.org/ftp/snapshot/dev/
测试用例参考网址
http://blog.itpub.net/31397003/viewspace-2139948/
Merge语法参考
[ 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
作用:判断源表和目标表是否满足合并的条件:
- 如果满足
- 用源表去更新目标表
- 用源表去删除目标表
- 什么也不干
- 如果不满足
- 用源表去插入目标表
- 什么也不干
这些组合有六种常用的使用模式，下面将进行测试：
创建测试表
create table a_merge (
id int not null,
name varchar not null,
year int
);
create table b_merge (
id int not null,
aid int not null,
name varchar not null,
year int,
city varchar
);
create table c_merge (
id int not null,
name varchar not null,
city varchar not null
);
创建之后的表结构如下：
postgres=# \d *merge
Table "public.a_merge"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | not null |
name | character varying | | not null |
year | integer | | |
Table "public.b_merge"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | not null |
aid | integer | | not null |
name | character varying | | not null |
year | integer | | |
city | character varying | | |
Table "public.c_merge"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | not null |
name | character varying | | not null |
city | character varying | | not null |
测试用例一：正常模式
先向a_merge和b_merge插入测试数据
insert into a_merge values(1,'liuwei',20);
insert into a_merge values(2,'zhangbin',21);
insert into a_merge values(3,'fuguo',20);
insert into b_merge values(1,2,'zhangbin',30,'吉林');
insert into b_merge values(2,4,'yihe',33,'黑龙江');
insert into b_merge (id,aid,name,city) values(3,3,'fuguo','山东');
此时a_merge和b_merge表中数据如下:
postgres=# select * from a_merge;select * from b_merge;
id | name | year
----+----------+------
1 | liuwei | 20
2 | zhangbin | 21
3 | fuguo | 20
(3 rows)
id | aid | name | year | city
----+-----+----------+------+--------
1 | 2 | zhangbin | 30 | 吉林
2 | 4 | yihe | 33 | 黑龙江
3 | 3 | fuguo | | 山东
(3 rows)
然后再使用b_merge来更新a_merge中的数据:
merge into a_merge a
using (select b.aid,b.name,b.year from b_merge b) c on (a.id=c.aid)
when matched then
update set year=c.year
when not matched then
insert values(c.aid,c.name,c.year);
此时a_merge表中的数据如下:
postgres=# select * from a_merge;
id | name | year
----+----------+------
1 | liuwei | 20
2 | zhangbin | 30
3 | fuguo |
4 | yihe | 33
(4 rows)
测试用例二：匹配则update模式
首先向b_merge中插入两条数据，为了体现出只update没有insert，必须有一个数据是a_merge中已经存在的，另一个数据是a_merge中不存在的，插入数据语句如下:
insert into b_merge values(4,1,'liuwei',80,'江西');
insert into b_merge values(5,5,'tiantian',23,'河南');
此时a_merge和b_merge表数据如下:
postgres=# select * from a_merge;select * from b_merge;
id | name | year
----+----------+------
1 | liuwei | 20
2 | zhangbin | 30
3 | fuguo |
4 | yihe | 33
(4 rows)
id | aid | name | year | city
----+-----+----------+------+--------
1 | 2 | zhangbin | 30 | 吉林
2 | 4 | yihe | 33 | 黑龙江
3 | 3 | fuguo | | 山东
4 | 1 | liuwei | 80 | 江西
5 | 5 | tiantian | 23 | 河南
(5 rows)
然后再次用b_merge来更新a_merge，但仅有update部分，没有insert部分。
merge into a_merge a
using (select b.aid,b.name,b.year from b_merge b) c on (a.id=c.aid)
when matched then
update set year=c.year;
merge完之后a_merge表数据如下:
postgres=# select * from a_merge;
id | name | year
----+----------+------
1 | liuwei | 80
2 | zhangbin | 30
3 | fuguo |
4 | yihe | 33
(4 rows)
可以发现仅对匹配的aid更新了年龄，没有插入不匹配的aid=5的数据。
测试用例三：不匹配则insert模式
首先改变b_merge中的一个数据，上一个测试新增的数据没有插入到a_merge，这次可以使用。
update b_merge set year=70 where aid=2;
此时a_merge和b_merge的表数据如下:
postgres=# select * from a_merge;select * from b_merge;
id | name | year
----+----------+------
1 | liuwei | 80
2 | zhangbin | 30
3 | fuguo |
4 | yihe | 33
(4 rows)
id | aid | name | year | city
----+-----+----------+------+--------
2 | 4 | yihe | 33 | 黑龙江
3 | 3 | fuguo | | 山东
4 | 1 | liuwei | 80 | 江西
5 | 5 | tiantian | 23 | 河南
1 | 2 | zhangbin | 70 | 吉林
(5 rows)
然后用b_merge来更新a_merge中的数据，此时只写了insert，没有写update:
merge into a_merge a
using (select b.aid,b.name,b.year from b_merge b) c on (a.id=c.aid)
when not matched then
insert values(c.aid,c.name,c.year);
此时a_merge的表数据如下:
postgres=# select * from a_merge;
id | name | year
----+----------+------
1 | liuwei | 80
2 | zhangbin | 30
3 | fuguo |
4 | yihe | 33
5 | tiantian | 23
(5 rows)
可以发现只有不匹配的aid=5的数据做了插入。
测试用例四：二次匹配
我们在on中进行join匹配之后，还可以在后面的when子句中对on筛选出来的记录再做一次条件判断，用来控制哪些要更新，哪些要插入，哪些要删除。
测试数据的sql代码如下，我们在b_merge修改了两个人名，并且增加了两个人员信息，但是他们来自的省份不同，所以我们可以通过添加省份条件来控制哪些能修改，哪些能插入:
update b_merge set name='yihe++' where id=2;
update b_merge set name='liuwei++' where id=4;
insert into b_merge values(6,6,'ningqin',23,'江西');
insert into b_merge values(7,7,'bing',24,'四川');
此时a_merge和b_merge的表数据如下:
postgres=# select * from a_merge;select * from b_merge;
id | name | year
----+----------+------
1 | liuwei | 80
2 | zhangbin | 30
3 | fuguo |
4 | yihe | 33
5 | tiantian | 23
(5 rows)
id | aid | name | year | city
----+-----+----------+------+--------
3 | 3 | fuguo | | 山东
5 | 5 | tiantian | 23 | 河南
1 | 2 | zhangbin | 70 | 吉林
2 | 4 | yihe++ | 33 | 黑龙江
4 | 1 | liuwei++ | 80 | 江西
6 | 6 | ningqin | 23 | 江西
7 | 7 | bing | 24 | 四川
(7 rows)
然后再用b_merge去更新a_merge，但是分别在insert和update后面添加了条件限制，控制数据的更新和插入:
merge into a_merge a
using (select b.aid,b.name,b.year,b.city from b_merge b) c on (a.id=c.aid)
when matched and c.city != '江西' then
update set name=c.name
when not matched and c.city = '江西' then
insert values(c.aid,c.name,c.year);
此时a_merge数据如下:
postgres=# select * from a_merge;
id | name | year
----+----------+------
1 | liuwei | 80
2 | zhangbin | 30
3 | fuguo |
4 | yihe++ | 33
5 | tiantian | 23
6 | ningqin | 23
(6 rows)
可以看到符合预期。
测试用例五：无条件的insert
我们要无条件全插入，则只需将on中条件设置为永假即可。
用b_merge来更新c_merge代码如下:
merge into c_merge c
using (select b.aid,b.name,b.city from b_merge b) b on (1=0)
when not matched then
insert values(b.aid,b.name,b.city);
此时c_merge表中的数据如下:
postgres=# select * from c_merge ;
id | name | city
----+----------+--------
3 | fuguo | 山东
5 | tiantian | 河南
2 | zhangbin | 吉林
4 | yihe++ | 黑龙江
1 | liuwei++ | 江西
6 | ningqin | 江西
7 | bing | 四川
(7 rows)
测试用例六：匹配则delete模式
在when匹配子句里除了update，也可以执行delete子句。
首先查看a_merge和b_merge表数据如下:
postgres=# select * from a_merge;
id | name | year
----+----------+------
1 | liuwei | 80
2 | zhangbin | 30
3 | fuguo |
4 | yihe++ | 33
5 | tiantian | 23
6 | ningqin | 23
(6 rows)
postgres=# select * from b_merge;
id | aid | name | year | city
----+-----+----------+------+--------
3 | 3 | fuguo | | 山东
5 | 5 | tiantian | 23 | 河南
1 | 2 | zhangbin | 70 | 吉林
2 | 4 | yihe++ | 33 | 黑龙江
4 | 1 | liuwei++ | 80 | 江西
6 | 6 | ningqin | 23 | 江西
7 | 7 | bing | 24 | 四川
(7 rows)
然后用b_merge来匹配删除a_merge，同时delete删除时进行二次匹配，只删除江西省份。
merge into a_merge a
using (select b.aid,b.name,b.year,b.city from b_merge b) c on (a.id=c.aid)
when matched and c.city = '江西' then
delete;
merge完之后a_merge表数据如下:
postgres=# select * from a_merge;
id | name | year
----+----------+------
2 | zhangbin | 30
3 | fuguo |
4 | yihe++ | 33
5 | tiantian | 23
(4 rows)
可以看到只有符合关联条件且是江西省份的两条数据才被删除。
保持联系
从2019年12月开始写第一篇文章，分享的初心一直在坚持，本人现在组建了一个PG乐知乐享交流群，欢迎关注我文章的小伙伴加我微信进群吹牛唠嗑，交流技术。
最后修改时间：2022-11-08 09:50:39
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
