Oracle 的merge into 语法转postgre
Oracle的merge into 语法
MERGE INTO t1
USING (SELECT id,name FROM t2) t2
ON ( t1.id=t2.id) //主键
WHEN MATCHED THEN //匹配则更新
UPDATE SET T1.name= t2.name
WHEN NOT MATCHED THEN //不匹配则插入
INSERT (id,name) VALUES (t2.id,t2.name);
merge into 的核心其实就是当有记录存在,则update;不存在,则insert。
pg对应的是upsert语法,其核心都是一样的,有则更新,无则插入;
在postgre9.5之前,其upsert语法如下:
WITH upsert AS (
UPDATE test1
SET col1 = test2.col1
FROM test2
WHERE test1.id = test2.id
RETURNING test1.*
)
INSERT INTO test01
SELECT *
FROM test2
WHERE NOT EXISTS (
SELECT 1
FROM upsert b
WHERE test2.id = b.id
);
在postgre9.5及之后,Oracle的 merge into 转化为 -- >PostgreSQL 的 INSERT INTO ... ON CONFLICT .. update...
例如:
insert into t1 select id,name from t2 ON CONFLICT(id) do update set name=excluded.name where t1.id=excluded.id;
也可以写成 insert into ()values()ON CONFLICT(主键列表) do update set name=excluded.name ... where ...