在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)
可以看到只有符合关联条件且是江西省份的两条数据才被删除。
保持联系
本人组建了一个技术交流群：PG乐知乐享交流群。欢迎关注文章的小伙伴随缘加入，进群请加本人微信skypkmoon并备注PG乐知乐享。
