PostgreSQL实现Oracle merge into功能

合并MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE

with upsert as (
    update a
        set col1 = b.col1,col2 = b.col2
        from b t
        where a.id = t.id and a.code = t.code
        returning a.id,a.code
)
insert
into a
select *
from b t
where not exists(
        select 1
        from upsert b
        where t.id = b.id
          and t.code = b.code
    );

merge into A a using B b
on (a.id = b.id and a.code = b.code)
when matched then
update col1 = b.col1,col2 = b.col2
    when not matched then
insert
into a
values (b.col1, b.col2);