PostgreSQL批量插入错误控制：如何将失败记录存入错误表

当然可以实现你要的需求——单条记录插入失败不终止脚本，同时把失败数据存入错误表。分两种场景给你方案：

场景1：仅处理唯一键/约束冲突（补充方案，供参考） #

如果只是主键/唯一键冲突这类特定错误，不需要函数也能实现，用CTE把未插入成功的记录导到错误表：

WITH source_data AS (
    SELECT uuid_generate_v4() AS id, t.code
    FROM sch.tab t
), inserted AS (
    INSERT INTO schema.table(id, code)
    SELECT id, code FROM source_data
    ON CONFLICT (id) DO NOTHING  -- 这里要明确冲突的约束列，比如id是主键
    RETURNING id
)
INSERT INTO error_table(id, code, error_msg, create_time)
SELECT sd.id, sd.code, '主键冲突：' || sd.id, NOW()
FROM source_data sd
LEFT JOIN inserted i ON sd.id = i.id
WHERE i.id IS NULL;

场景2：处理所有插入错误（通用方案，适配你的需求） #

如果要覆盖所有可能的插入错误（比如数据类型不匹配、外键约束违反、非空约束等），就得用PL/pgSQL函数结合异常捕获来逐条处理：

第一步：创建错误表 #

先建一张用来存失败记录的表，建议加上错误信息和时间字段方便排查：

CREATE TABLE IF NOT EXISTS error_table (
    id UUID,
    code TEXT,  -- 这里类型要和源表的code字段一致，根据实际情况调整
    error_msg TEXT NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT NOW()
);

第二步：编写迁移函数 #

这个函数会遍历源表记录，尝试插入目标表，失败的话就把数据和错误信息存入错误表：

CREATE OR REPLACE FUNCTION migrate_data_with_error_log()
RETURNS VOID AS $$
DECLARE
    rec RECORD;
    v_id UUID;
BEGIN
    -- 遍历源表的所有记录
    FOR rec IN SELECT code FROM sch.tab LOOP
        v_id := uuid_generate_v4();
        BEGIN
            -- 尝试插入目标表
            INSERT INTO schema.table(id, code) VALUES (v_id, rec.code);
        EXCEPTION
            WHEN OTHERS THEN
                -- 捕获所有异常，写入错误表
                INSERT INTO error_table(id, code, error_msg)
                VALUES (v_id, rec.code, SQLERRM);
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

第三步：执行迁移 #

直接调用函数就能开始迁移，过程中失败的记录会自动存入错误表：

SELECT migrate_data_with_error_log();

注意事项 #

• 如果源表数据量极大，逐条处理的效率会比批量插入低。如果要平衡效率和错误捕获，可以改成批量分组处理（比如每次处理100条），批量插入失败后再逐条排查，但实现复杂度会高一些。
• 确保错误表的字段类型和源数据完全匹配，避免因为类型问题导致错误记录也存不进去。
• SQLERRM会返回PostgreSQL原生的错误信息，比如"null value in column "code" violates not-null constraint"，能帮你快速定位失败原因。

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