pg数据库 实现merge

merge 语法用于将一张表的数据合并到另外一张表。根据某个唯一条件插入或更新数据。可以用于

• 数据同步场景，做增量更新
• 结合业务计算，实现复杂业务逻辑

因为是在一个语句中实现了插入和更新的处理，所以看起来更加简洁、高效。

Oracle Merge实现（鼻祖）

--标准语法（用 b 表中值更新 a 表中的值）
MERGE INTO target_table a
USING source_table b
ON (a.pkid = b.pkid) --a 和 b 的关联条件必须保证唯一，否则会报错
WHEN MATCHED THEN
    UPDATE SET 
      column1 = b.value1, 
      column2 = b.value2, 
      ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...)
    VALUES (b.value1, b.value2, ...);

--举例
MERGE INTO dwd.employees a
USING (select * from ods.employees) b
ON (a.employee_id = b.employee_id)
WHEN MATCHED THEN
    UPDATE SET name = b.name, salary = b.salary --matched 也支持使用 delete
WHEN NOT MATCHED THEN
    INSERT (employee_id, name, salary)
    VALUES (b.employee_id, b.name, b.salary);

注意点：

• when matched 子句定义了当源表和目标表中的记录匹配时要执行的操作；
• when not matched 子句定义了当源表中的记录在目标表中不存在时要执行的操作；
• merge 语句中可以使用复杂的表达式和条件；
• 使用 merge 时，要确保 on 条件的匹配逻辑是准确的，且推荐在 on 条件上建立索引；
• 在高并发情况下，可能会导致重复更新或插入，要考虑加锁控制；
• merge 语句可以提高数据处理的效率，因为它可以在单个操作中进行更新和插入；

PostgreSQL Merge实现

pg15 版本及以上

pg 在 15 版本以上引入了 merge 语句。语法与 上面的Oracle 一样。唯一与 Oracle 语法的区别在于 pg 允许多个 when matched 和 when not matched

示例如下

MERGE INTO employees E
USING new_employees N
ON (E.employee_id = N.employee_id)
WHEN MATCHED AND E.salary < N.salary THEN
    UPDATE SET salary = N.salary  -- 更新薪资
WHEN MATCHED AND E.salary >= N.salary THEN
    DELETE  -- 删除记录
WHEN NOT MATCHED THEN
    INSERT (employee_id, name, salary)
    VALUES (N.employee_id, N.name, N.salary);  -- 插入新员工

 pg 旧版本

在15版本之前，可以使用 insert ... on conflict 或者用 update/insert 的组合来模拟 merge 功能。

示例如下

--方法一
INSERT INTO table_name --confilict条件无法匹配的执行插入
(uid, column1, column2)
VALUES (
    select id, value1, value2 from table_name_2
)
ON CONFLICT (uid) --要保证 uid 和 id 是可唯一关联的
DO UPDATE SET --uid 和 id 关联上的就会执行 update
    column1 = excluded.column1, column2 = excluded.column2;

--方法二
--1.关联上执行更新操作
update table_name 
set col1 = val1, col2 = val2
where condition;

--2.没关联上的执行插入操作
insert into table_name(col1, col2)
select val1, val2 
from table_name_2
where not exists(select 1 from table_name where condition)