合并在postgres中 [英] Merge in postgres

问题描述

我正在尝试将下面的oracle查询转换为postgres，

Am trying to convert below oracle query to postgres,

MERGE INTO table1 g
USING (SELECT distinct g.CDD , d.SGR
from  table2 g, table3 d
where g.IDF = d.IDF) f
ON (g.SGR = f.SGR and g.CDD = f.CDD)
   WHEN NOT MATCHED THEN
   INSERT (SGR, CDD)
   VALUES (f.SGR, f.CDD);

我做了以下与postgres兼容的更改:

I made changes as below compatible to postgres:

WITH f AS (
SELECT distinct g.CDD , d.SGR
from  table2 g, table3 d
where g.IDF = d.IDF
),
upd AS (
update table1 g 
set 
SGR = f.SGR , CDD = f.CDD 
FROM f where g.SGR = f.SGR  and g.CDD = f.CDD  
returning g.CDD, g.SGR
)
INSERT INTO table1(SGR, CDD ) SELECT f.SGR, f.CDD FROM f;

但是令人怀疑的是，如果数据匹配，我的oracle查询不会更新任何列，但是无法相应地对其进行转换.有人可以帮我改正吗?

But am doubtful ,my oracle query is not updating any columns if data matched , but am unable to convert it accordingly . Can anyone help me to correct it ?

推荐答案

假设您在(sgr，cdd)上具有主键(或唯一键)，则可以将其转换为 insert...关于冲突声明:

Assuming you have a primary (or unique) key on (sgr, cdd) you can convert this to an insert ... on conflict statement:

insert into table1 (SGR, CDD)
select  distinct g.CDD, d.SGR
from table2 g
  join table3 d ON g.IDF = d.IDF
on conflict (cdd, sgr) do nothing;

---

如果您没有唯一的约束(这里有一个问题:为什么?)，那么简单的INSERT ... SELECT语句应该可以工作(在Oracle中也可以工作).

---

If you don't have a unique constraint (which bears the question: why?) then a straight-forward INSERT ... SELECT statement should work (which would have worke in Oracle as well).

WITH f AS (
   SELECT distinct g.CDD, d.SGR
   from table2 g
     join table3 d on g.IDF = d.IDF
)
INSERT INTO table1 (SGR, CDD) 
SELECT f.SGR, f.CDD 
FROM f
WHERE NOT EXISTS (select *
                  from table1 t1
                     join f on (t1.sgr, t1.cdd) = (f.cdd, f.sgrf));

请注意，对于并发执行来说，这不是安全的(Oracle的MERGE语句也不安全).您仍然可以在table1中获得重复的值(关于(sgr，cdd)的组合).

Note that this is NOT safe for concurrent execution (and neither is Oracle's MERGE statement). You can still wind up with duplicate values in table1 (with regards to the combination of (sgr,cdd)).

防止重复的唯一明智的方法是创建唯一索引(或约束)-这将使您能够使用效率更高的在冲突中插入.您应该真的考虑一下，如果您的业务规则不允许重复.

The only sensible way to prevent duplicates is to create a unique index (or constraint) - which would enable you to use the much more efficient insert on conflict. You should really consider that if your business rules disallow duplicates.

请注意，我将WHERE子句中的古老的，隐式的联接转换为现代的，显式的 JOIN 运算符，但这不是必需的.

Note that I converted your ancient, implicit join in the WHERE clause to a modern, explicit JOIN operator, but it is not required for this to work.

这篇关于合并在postgres中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！