oracle迁移postsql的,osdba's blog : Oracle迁移PostgreSQL系列文章之二：merge语句

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);