PostgreSQL+Flyway数据迁移时插入返回ID并回填旧表的实现问题

核心实现逻辑 #

PostgreSQL 原生支持 INSERT 语句的 RETURNING 子句，可以在插入数据的同时返回新生成的行数据，搭配 CTE 即可一步完成插入+关联回填的需求，不需要额外编写业务代码适配。

适配场景1：旧表 name + user_id 组合唯一 #

如果你的旧表不存在name和user_id都相同的重复行，可以直接用如下SQL：

WITH inserted_data AS (
  -- 插入数据到新表，同时返回新生成的主键和对应匹配字段
  INSERT INTO Table_2 (name2, user_id2)
  SELECT name, user_id FROM Table_1
  RETURNING id2, name2, user_id2
)
-- 关联更新旧表的关联字段
UPDATE Table_1
SET migrated_table_2_id = inserted_data.id2
FROM inserted_data
WHERE Table_1.name = inserted_data.name2 
AND Table_1.user_id = inserted_data.user_id2;

适配场景2：旧表存在重复数据（更稳妥的通用方案） #

如果存在重复行，建议临时给新表加一个旧表ID存储字段，迁移完成后可删除，完全避免关联错误：

-- 临时新增旧表ID关联字段
ALTER TABLE Table_2 ADD COLUMN IF NOT EXISTS temp_old_table1_id INT;

WITH inserted_data AS (
  -- 插入时携带旧表ID
  INSERT INTO Table_2 (name2, user_id2, temp_old_table1_id)
  SELECT name, user_id, id FROM Table_1
  RETURNING id2, temp_old_table1_id
)
-- 用主键ID关联，100%不会匹配错误
UPDATE Table_1
SET migrated_table_2_id = inserted_data.id2
FROM inserted_data
WHERE Table_1.id = inserted_data.temp_old_table1_id;

-- 迁移完成后可选删除临时字段
ALTER TABLE Table_2 DROP COLUMN IF EXISTS temp_old_table1_id;

Flyway 使用说明 #

• 可以把完整SQL写入同一个Flyway版本迁移文件，PostgreSQL支持事务内执行DDL+DML，迁移失败会自动全量回滚，不会产生脏数据
• 生产环境大表迁移可以在SELECT语句后加WHERE migrated_table_2_id IS NULL条件分批执行，避免长时间锁表影响业务

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