postgresql新特性之Merge

创建测试表

 create table cps.public.test(
 id integer primary key,
 balance numeric,
 status varchar(1)
 );

测试案例

官网介绍

merge into test t using (
select 1 id,0 balance,'Y' status) s 
on(t.id = s.id)
-- 当匹配上了,status = 'N',然后删除这条记录
when matched and t.status = 'N' then delete 
-- 当匹配上了
when matched then 
update set 
balance = s.balance,status = s.status
-- 当不匹配
when not matched then
insert (id,balance,status)
values(s.id,s.balance,s.status);

执行结果