PostgreSQL单条纯SQL实现1:n转1:1的批量插入更新方案

解决方案 #

可以通过单条纯PostgreSQL SQL实现需求，核心利用PostgreSQL可写CTE + INSERT ... RETURNING特性完成批量插入、映射关联、更新、清理全链路逻辑，无需依赖存储过程或外部代码：

WITH baz_with_old_foo AS (
    -- 关联获取每个baz对应的原foo名称，按baz.id排序确保映射关系稳定
    SELECT b.id AS baz_id, f.name AS foo_name
    FROM baz b
    JOIN foo f ON b.foo_id = f.id
    ORDER BY b.id
),
inserted_foo AS (
    -- 批量插入新foo记录，返回生成的自增ID并按顺序打行号
    INSERT INTO foo (name)
    SELECT foo_name FROM baz_with_old_foo ORDER BY baz_id
    RETURNING id, ROW_NUMBER() OVER (ORDER BY id) AS rn
),
baz_new_foo_mapping AS (
    -- 给原baz列表也按顺序打行号，和新插入的foo记录一一映射
    SELECT 
        bw.baz_id,
        ifoo.id AS new_foo_id
    FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY baz_id) AS rn 
        FROM baz_with_old_foo
    ) bw
    JOIN inserted_foo ifoo ON bw.rn = ifoo.rn
),
update_baz AS (
    -- 批量更新baz的关联foo_id为新生成的ID
    UPDATE baz b
    SET foo_id = bnfm.new_foo_id
    FROM baz_new_foo_mapping bnfm
    WHERE b.id = bnfm.baz_id
    RETURNING 1
)
-- 最后删除没有被baz关联的旧foo孤立记录
DELETE FROM foo
WHERE id NOT IN (SELECT DISTINCT foo_id FROM baz);

注意事项 #

1. 该方案为PostgreSQL专用，依赖PostgreSQL特有的可写CTE和INSERT ... RETURNING语法
2. 执行前请务必备份数据，建议先在测试环境验证逻辑符合预期再在生产环境执行
3. 若baz表数据量极大，建议分批次执行避免长时间锁表影响线上业务

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