如何将数据从旧表迁移到带外键关联的新表（含逻辑处理）

以下是满足需求的PL/pgSQL函数，实现将旧表user_1和post_1的数据迁移到带外键关联的新表user_2和post_2：

CREATE OR REPLACE FUNCTION migrate_old_to_new()
RETURNS VOID AS $$
DECLARE
    v_total_rows INTEGER;
    v_old_user_id VARCHAR;
    v_name VARCHAR;
    v_address TEXT;
    v_phone_number VARCHAR;
    v_new_user_id UUID;
BEGIN
    -- 获取user_1的总行数
    v_total_rows := rowsnumber();

    -- 循环处理每一行旧用户数据
    FOR i IN 1..v_total_rows LOOP
        -- 按循环索引提取对应行的旧用户数据
        SELECT id, name, address, phone_number
        INTO v_old_user_id, v_name, v_address, v_phone_number
        FROM (
            SELECT *, ROW_NUMBER() OVER () AS row_num
            FROM user_1
        ) AS user_rows
        WHERE row_num = i;

        -- 插入新用户表并获取自动生成的UUID
        INSERT INTO user_2(name, address, phone_number)
        VALUES (v_name, v_address, v_phone_number)
        RETURNING id INTO v_new_user_id;

        -- 将该用户对应的旧帖子插入新帖子表，关联新生成的用户UUID
        INSERT INTO post_2(user_id, title, body)
        SELECT v_new_user_id, title, body
        FROM post_1
        WHERE user_id = v_old_user_id;
    END LOOP;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE '迁移出错：%', SQLERRM;
END;
$$ LANGUAGE plpgsql;

关键说明 #

• 循环索引提取数据：通过ROW_NUMBER()窗口函数为user_1的每行数据生成行号，配合循环索引定位到目标行
• 获取新UUID：使用INSERT ... RETURNING语法直接获取user_2中自动生成的UUID，无需额外查询
• 外键关联建立：将新生成的用户UUID作为post_2的user_id插入，确保帖子与新用户的关联关系正确
• 异常处理：添加了异常捕获逻辑，迁移出错时自动回滚并输出错误信息

执行迁移只需调用函数：

SELECT migrate_old_to_new();

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