PostgreSQL类似Oracle MERGE功能的实现

概述

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。