Oracle含LEVEL/CONNECT BY的MERGE语句迁移PostgreSQL求助

典型Oracle原代码示例 #

假设你的Oracle MERGE语句逻辑如下（层级数据同步场景）：

MERGE INTO target_table t
USING (
    SELECT 
        id, parent_id, name, LEVEL as lvl
    FROM source_table
    START WITH parent_id IS NULL
    CONNECT BY PRIOR id = parent_id
) s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET t.parent_id = s.parent_id, t.name = s.name, t.lvl = s.lvl
WHEN NOT MATCHED THEN
    INSERT (id, parent_id, name, lvl)
    VALUES (s.id, s.parent_id, s.name, s.lvl);

PostgreSQL 完整迁移方案 #

方案1：PostgreSQL 11+（原生支持MERGE） #

用WITH RECURSIVE替代Oracle的层级查询语法，再结合PG原生MERGE实现同步：

WITH RECURSIVE recursive_source AS (
    -- 对应Oracle的START WITH子句：起始节点
    SELECT 
        id, parent_id, name, 1 AS lvl
    FROM source_table
    WHERE parent_id IS NULL
    UNION ALL
    -- 对应Oracle的CONNECT BY PRIOR：递归关联父节点
    SELECT 
        s.id, s.parent_id, s.name, rs.lvl + 1 AS lvl
    FROM source_table s
    JOIN recursive_source rs ON rs.id = s.parent_id
)
MERGE INTO target_table t
USING recursive_source s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET 
        parent_id = s.parent_id,
        name = s.name,
        lvl = s.lvl
WHEN NOT MATCHED THEN
    INSERT (id, parent_id, name, lvl)
    VALUES (s.id, s.parent_id, s.name, s.lvl);

方案2：PostgreSQL 10及以下版本（无原生MERGE） #

通过UPDATE+INSERT ... ON CONFLICT的组合模拟MERGE逻辑：

WITH RECURSIVE recursive_source AS (
    SELECT 
        id, parent_id, name, 1 AS lvl
    FROM source_table
    WHERE parent_id IS NULL
    UNION ALL
    SELECT 
        s.id, s.parent_id, s.name, rs.lvl + 1 AS lvl
    FROM source_table s
    JOIN recursive_source rs ON rs.id = s.parent_id
),
-- 先执行更新，返回已更新的ID
updated_rows AS (
    UPDATE target_table t
    SET 
        parent_id = s.parent_id,
        name = s.name,
        lvl = s.lvl
    FROM recursive_source s
    WHERE t.id = s.id
    RETURNING t.id
)
-- 插入未匹配的新数据
INSERT INTO target_table (id, parent_id, name, lvl)
SELECT id, parent_id, name, lvl
FROM recursive_source s
WHERE s.id NOT IN (SELECT id FROM updated_rows);

核心迁移要点 #

• 层级查询替换：Oracle的START WITH ... CONNECT BY PRIOR完全等价于PostgreSQL的WITH RECURSIVE，其中：
  • 递归CTE的第一部分对应START WITH的起始条件
  • 第二部分的JOIN逻辑对应CONNECT BY PRIOR的父子关联
  • Oracle的LEVEL字段通过递归CTE中自增的lvl字段实现
• MERGE逻辑适配：
  • PG 11+的MERGE语法与Oracle基本一致，仅需注意不支持Oracle中部分特殊子句（如UPDATE ... WHERE需移至WHEN MATCHED AND <条件>）
  • 低版本PG必须拆分UPDATE和INSERT两步，用CTE串联执行逻辑

内容的提问来源于stack exchange，提问作者Hoper