PostgreSQL复制表行并获取新旧ID对应关系的可行方案

问题背景 #

现有表结构：

CREATE TABLE mytbl (
  id int PRIMARY KEY generated by default as identity,
  col1 int,
  col2 text, ...
);

需要复制该表满足条件的部分行，同时获取新旧ID的对应关系（用于后续复制关联表数据）。尝试的写法报错：ERROR: missing FROM-clause entry for table "old"，原语句如下：

insert into mytbl (col1, col2)
    select col1, col2
        from mytbl old
        where col1 = 999 -- some condition
    returning 
        old.id as old_id,
        id as new_id;

可行解决方案 #

可以利用PostgreSQL的CTE（公共表表达式）实现需求，无需修改表结构、循环操作或事后关联，直接在插入过程中返回新旧ID对应关系：

WITH selected_rows AS (
    SELECT id AS old_id, col1, col2
    FROM mytbl
    WHERE col1 = 999 -- 替换为你的筛选条件
)
INSERT INTO mytbl (col1, col2)
SELECT col1, col2 FROM selected_rows
RETURNING selected_rows.old_id, id AS new_id;

原理说明 #

原语句报错的核心原因：RETURNING子句的上下文是目标表，无法直接引用源表的别名（old）。通过CTE先暂存需要复制的行（包含原ID），再从CTE读取数据插入目标表，此时RETURNING可直接引用CTE中的old_id，同时获取目标表生成的新ID。

与你考虑的方案对比 #

• 循环逐行复制：效率低下，批量操作的CTE写法性能更优，代码更简洁。
• 新增old_id列：无需额外存储冗余字段，CTE写法避免了表结构变更。
• 事后关联查询：无需依赖复杂的关联条件，插入时直接获取对应关系，结果更可靠。

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