将 Oracle MERGE 语句迁移到 PostgreSQL UPSERT 语句 [英] Migrating an Oracle MERGE statement to a PostgreSQL UPSERT statement

问题描述

能否请您帮我将以下 Oracle MERGE 语句转换为有效的 UPSERT 语句以在 PostgreSQL 9.3 数据库中使用?

Can you please help me converting the following Oracle MERGE statement into a valid UPSERT statement for use in a PostgreSQL 9.3 database?

MERGE INTO my_table a
     USING (SELECT v_c1 key,
                   v_c2 AS pkey,
                   v_c3 AS wcount,
                   v_c4 AS dcount
              FROM DUAL) b
        ON (    a.key = b.key
            AND a.pkey = b.pkey
WHEN MATCHED
THEN
   UPDATE SET wcount = b.wcount,
              dcount = b.dcount
WHEN NOT MATCHED
THEN
   INSERT     (key,
               pkey,
               wcount,
               dcount)
    VALUES(b.key,b.pkey,b.wcount,b.dcount);

推荐答案

我认为 PostgreSQL 9.3 中没有 UPSERT 语句，但您可以这样做:

I don't think there's UPSERT statement in PostgreSQL 9.3, but you can do this:

with cte_dual as (
    select
        v_c1 as key,
        v_c2 as pkey,
        v_c3 as wcount,
        v_c4 as dcount
), cte_update as (
    update my_table as a set
        wcount = b.wcount,
        dcount = b.dcount
    from cte_dual as b
    where b.key = a.key and b.pkey = a.pkey
    returning *
)
insert into my_table (key, pkey, wcount, dcount)
select d.key, d.pkey, d.wcount, d.dcount
from cte_dual as d
where not exists (select * from cte_update as u WHERE u.key = d.key and u.pkey = d.pkey)

您可以阅读几个类似的问题:

You can read couple of similar questions:

• 如何进行UPSERT(MERGE, INSERT ... ON DUPLICATE UPDATE) 在 PostgreSQL 中?
• 在 PostgreSQL 中插入重复更新?

这篇关于将 Oracle MERGE 语句迁移到 PostgreSQL UPSERT 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！