PostgreSQL 9.5中如何克隆记录并维护新旧ID映射

你遇到的错误是因为PostgreSQL的RETURNING子句无法直接引用INSERT语句中SELECT部分的表别名s——RETURNING的作用域只能访问目标表的列，或者SELECT列表中明确列出的表达式，不能跨作用域引用外部CTE的表。

下面提供两种可靠的解决方案：

方案1：利用行号关联（无数据唯一性依赖，推荐） #

通过给源数据添加唯一行号，插入时保持顺序，再给新插入的行添加对应顺序的行号，最后通过行号关联得到新旧ID映射。这种方法不依赖业务列的唯一性，适用于所有场景：

CREATE SCHEMA stackoverflow;

CREATE TABLE stackoverflow.clone_problem
(
    id bigserial PRIMARY KEY NOT NULL,
    some_id bigint NULL,
    some_other_id bigint NULL,
    modified_time timestamp NOT NULL DEFAULT now(),
    modified_by varchar(128) NOT NULL DEFAULT current_user
);
  
INSERT INTO stackoverflow.clone_problem
(
   id,
   some_id,
   some_other_id
)
VALUES (1,1,1)
      ,(2,2,2)
      ,(3,3,3);

WITH sources
AS 
(
    SELECT 
        id as old_id,
        some_id,
        some_other_id,
        -- 给源数据分配唯一行号
        row_number() OVER () as row_num
    FROM stackoverflow.clone_problem
    WHERE id = ANY('{1,3}')
),  
inserts
AS 
(
    INSERT INTO stackoverflow.clone_problem
    (some_id, some_other_id)
    SELECT 
        s.some_id,
        s.some_other_id
    FROM sources s
    ORDER BY row_num  -- 按行号排序，保证插入顺序和源数据完全一致
    RETURNING 
        id as new_id,
        -- 给新插入的行分配同顺序的行号
        row_number() OVER () as row_num
)
-- 通过行号关联，得到新旧ID的映射关系
SELECT 
    i.new_id,
    s.old_id
FROM inserts i
JOIN sources s ON i.row_num = s.row_num;

方案2：通过业务列关联（依赖业务列唯一性） #

如果你的some_id和some_other_id组合是唯一的，可以先插入数据，再通过这两个业务列关联源数据和新数据，获取映射：

CREATE SCHEMA stackoverflow;

CREATE TABLE stackoverflow.clone_problem
(
    id bigserial PRIMARY KEY NOT NULL,
    some_id bigint NULL,
    some_other_id bigint NULL,
    modified_time timestamp NOT NULL DEFAULT now(),
    modified_by varchar(128) NOT NULL DEFAULT current_user
);
  
INSERT INTO stackoverflow.clone_problem
(
   id,
   some_id,
   some_other_id
)
VALUES (1,1,1)
      ,(2,2,2)
      ,(3,3,3);

WITH sources
AS 
(
    SELECT 
        id as old_id,
        some_id,
        some_other_id
    FROM stackoverflow.clone_problem
    WHERE id = ANY('{1,3}')
),  
inserts
AS 
(
    INSERT INTO stackoverflow.clone_problem
    (some_id, some_other_id)
    SELECT s.some_id, s.some_other_id
    FROM sources s
    RETURNING 
        id as new_id,
        some_id,
        some_other_id
)
SELECT 
    i.new_id,
    s.old_id
FROM inserts i
JOIN sources s 
    ON i.some_id = s.some_id 
    AND i.some_other_id = s.some_other_id;

方案对比 #

• 方案1：无需业务列唯一，兼容性更强，适合大多数场景；
• 方案2：代码更简洁，但必须保证some_id+some_other_id的组合唯一，否则会出现关联错误。

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