在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

作用:判斷源表和目標表是否滿足合并的條件:

1. 如果滿足

• 用源表去更新目標表
• 用源表去刪除目標表
• 什么也不干

2. 如果不滿足

• 用源表去插入目標表
• 什么也不干

這些組合有六種常用的使用模式，下面將進行測試：

創建測試表

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樂知樂享交流群，歡迎關注我文章的小伙伴加我微信進群吹牛嘮嗑，交流技術。