PostgreSQL類似OracleMERGE功能的實現

概述

MERGE語句是Oracle9i新增的語法，用來合併UPDATE和INSERT語句。通過MERGE語句，根據一張表或子查詢的連線條件對另外一張表進行查詢，連線條件匹配上的進行UPDATE，無法匹配的執行INSERT。這個語法僅需要一次全表掃描就完成了全部工作，執行效率要高於INSERT＋UPDATE

而PostgreSQL中不直接支援這個語法，但PostgreSQL可以使用WITH Queries (Common Table Expressions)的方法實現相同的功能。

下面我們就來一起看一下

語句詳解

下面這條SQL是把test2表中的資料merge到test1表中，其中主鍵欄位為id

WITH upsert AS (
        UPDATE test1
        SET col1 = test2.col1
        FROM test2
        WHERE test1.id = test2.id
        RETURNING test1.*
    )
INSERT INTO test01
SELECT *
FROM test2
WHERE NOT EXISTS (
    SELECT 1
    FROM upsert b
    WHERE test2.id = b.id
);

其實這段SQL的的重點就是利用了postgresql的一個update特性————RETURNING，返回一個update的結果集，因為查詢條件的存在(也因為它是主鍵，是唯一)，就會將兩張表重疊的部分給過濾出來，再用where not exists將這些重疊的部分給忽略掉。這樣就將資料merge進去了

小測試

建兩張表

postgres=# create table test1(id int primary key,name text);
CREATE TABLE
postgres=# 
postgres=# create table test2(id int primary key,name text);
CREATE TABLE

資料部分重疊

postgres=# select * from test1;
 id | name  
----+-------
  1 | aaaaa
  2 | aaaaa
  3 | aaaaa
  4 | aaaaa
  5 | aaaaa
(5 rows)

postgres=# select * from test2;
 id | name  
----+-------
  4 | aaaaa
  5 | aaaaa
  6 | bbbbb
  7 | bbbbb
  8 | bbbbb
  9 | bbbbb
(6 rows)

執行merge語句

用test2 這張表去更新test1 ，會將test1 中沒有的資料插入，有的則不會改變

postgres=# WITH upsert AS (
  UPDATE test1
  SET name = test2.name
  FROM test2
  WHERE test1.id = test2.id
  RETURNING test1.*
 )
INSERT INTO test1
SELECT *
FROM test2
WHERE NOT EXISTS (
 SELECT 1
 FROM upsert b
 WHERE test2.id = b.id
);
INSERT 0 4

postgres=# select * from test1;
 id | name  
----+-------
  1 | aaaaa
  2 | aaaaa
  3 | aaaaa
  4 | aaaaa
  5 | aaaaa
  6 | bbbbb
  7 | bbbbb
  8 | bbbbb
  9 | bbbbb
(9 rows)

可以看到，資料已經更新進來了

一個注意點

在我實際的業務場景更新中，我發現一個需要注意的地方，那就是客戶的test2表，ID列不是主鍵，且有許多重複
檢視ID列有多少重複的SQL如下，如果為0，則說明沒有重複值

select count(*) from users_purse where id in (select id from users_purse group by id having COUNT(*)>1）

如果遇到這種情況，有可能就會出錯，因為test1.ID是不可重複的，所以就可能需要先對其做去重處理distinct。