PG 15devel Merge Into语法测试

原创多米爸比 2022-03-31

3652

在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)
复制

可以看到只有符合关联条件且是江西省份的两条数据才被删除。