Oracle迁移PostgreSQL系列文章之二:merge语句

Posted on 2015-03-06 11:12:46 by osdba

我们知道,Oracle中有一个特别的merge语句。而PostgreSQL中不直接支持这个语法,但PostgreSQL可以使用WITH Queries (Common Table Expressions)的方法实现相同的功能。Common Table Expression在PostgreSQL常缩写为CTE。

假设oracle中有一张表:

CREATE TABLE test01(id number, note varchar2(32));

orale中的merge语句的SQL如下:

-- 第一条merge语句:
merge into test01 a
  using (select 1 as id, 'xxxx' as note from dual) b on (a.id=b.id)
  when matched then update set a.note=b.note
  when not matched then insert (a.id, a.note) values (b.id, b.note);

-- 第二条merge语句
merge into test01 a
  using (select 1 as id, 'yyyy' as note from dual) b on (a.id=b.id)
  when matched then update set a.note=b.note
  when not matched then insert (a.id, a.note) values (b.id, b.note);

转换成PostgreSQL的语法如下:

CREATE TABL test01(id int, note varchar(32));

-- 第一条merge语句
WITH upsert as  
(update test01 m set note='xxxx' where id= 1
  RETURNING m.*
), data as (select 1 as id, 'xxxx' as note)
insert into test01 select * from data a where not exists(select 1 from upsert b where a.id=b.id);

-- 第二条merge语句
WITH upsert as  
(update test01 m set note='yyyy' where id= 1
  RETURNING m.*
), data as (select 1 as id, 'yyyy' as note)
insert into test01 select * from data a where not exists(select 1 from upsert b where a.id=b.id);