SQL跨表迁移数据并保留第三方表外键关联的实现方案

核心问题 #

最初的报错源于INSERT...RETURNING子句无法直接引用源表old_metadata的列，而现有可行方案需要临时添加字段，增加了不必要的操作步骤。

优化后的简洁方案 #

无需给任何表添加临时字段，利用PostgreSQL的CTE（公共表表达式）直接保留新旧ID的映射关系，一次性完成数据插入和外键更新：

-- 一步完成new_metadata数据插入 + deposits外键更新
WITH inserted_metadata AS (
    INSERT INTO new_metadata (amount, metadata_type)
    SELECT amount, 'OLD_METADATA'
    FROM old_metadata om
    RETURNING id AS new_id, om.id AS old_id
)
UPDATE deposits d
SET metadata_id = im.new_id
FROM inserted_metadata im
WHERE d.metadata_id = im.old_id;

方案说明 #

1. CTE inserted_metadata：
   • 从old_metadata选取数据插入到new_metadata，指定metadata_type为OLD_METADATA
   • 通过RETURNING同时返回新生成的new_metadata.id（别名new_id）和源表old_metadata.id（别名old_id），建立新旧ID的映射关系
2. UPDATE语句：
   • 关联deposits和inserted_metadata的映射关系，将deposits.metadata_id直接更新为对应的新表ID

可选扩展（保留历史外键） #

如果需要保留原metadata_id字段作为历史记录，可以先给deposits添加字段存储旧值，再执行核心更新：

-- 可选：保留旧外键值作为历史
ALTER TABLE deposits ADD COLUMN old_metadata_id INT;
UPDATE deposits SET old_metadata_id = metadata_id;

-- 执行核心迁移更新
WITH inserted_metadata AS (
    INSERT INTO new_metadata (amount, metadata_type)
    SELECT amount, 'OLD_METADATA'
    FROM old_metadata om
    RETURNING id AS new_id, om.id AS old_id
)
UPDATE deposits d
SET metadata_id = im.new_id
FROM inserted_metadata im
WHERE d.metadata_id = im.old_id;

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