PostgreSQL中复制带外键关联表行并维护新记录关联

要让新Worker记录关联到新生成的Employer记录，核心是建立原EmployerID与新EmployerID的映射关系，再用这个映射替换Worker表中的旧EmployerID。以下是两种可行的实现方式：

方式一：使用WITH子句一次性完成（推荐） #

通过CTE（公共表表达式）在同一个SQL语句中完成Employer复制、映射建立和Worker复制，无需临时表：

WITH copied_employers AS (
  -- 复制Employer并关联原记录，生成原ID与新ID的映射
  SELECT original.id AS original_employer_id, new.id AS new_employer_id
  FROM (
    -- 插入新的Employer记录
    INSERT INTO employer (name, service_provider_id, updated_at)
    SELECT name, 999, updated_at
    FROM employer
    WHERE service_provider_id = 11
    RETURNING id, name  -- 返回新记录的ID和名称，用于关联原记录
  ) new
  -- 通过名称+原服务商ID关联原Employer记录，获取原ID
  JOIN employer original 
    ON original.name = new.name 
    AND original.service_provider_id = 11
)
-- 复制Worker时，用映射关系替换EmployerID
INSERT INTO worker (name, service_provider_id, employer_id)
SELECT w.name, 999, ce.new_employer_id
FROM worker w
JOIN copied_employers ce ON w.employer_id = ce.original_employer_id
WHERE w.service_provider_id = 11;

方式二：使用临时表存储映射关系 #

适合需要多次使用映射的场景，步骤更清晰：

-- 1. 复制Employer并生成原ID与新ID的映射表
CREATE TEMP TABLE employer_mapping AS
SELECT original.id AS original_employer_id, new.id AS new_employer_id
FROM (
  INSERT INTO employer (name, service_provider_id, updated_at)
  SELECT name, 999, updated_at
  FROM employer
  WHERE service_provider_id = 11
  RETURNING id, name
) new
JOIN employer original 
  ON original.name = new.name 
  AND original.service_provider_id = 11;

-- 2. 复制Worker，使用映射替换旧EmployerID
INSERT INTO worker (name, service_provider_id, employer_id)
SELECT w.name, 999, em.new_employer_id
FROM worker w
JOIN employer_mapping em ON w.employer_id = em.original_employer_id
WHERE w.service_provider_id = 11;

-- 可选：临时表会在会话结束后自动删除，手动删除也可
DROP TABLE employer_mapping;

注意事项 #

• 上述示例通过name + service_provider_id关联原记录和新记录，请确保这个组合在Employer表中是唯一的，否则会出现关联错误。如果有其他唯一标识（比如业务主键），请替换为对应的字段。
• 如果需要复制更多关联表（比如Worker的子表），可以复用同样的映射逻辑，逐层替换关联ID。

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