双向关联表的PostgreSQL数据库CSV数据导入最佳实践咨询

针对双向外键关联的表（表A与表B互为外键）使用COPY ... FROM ...导入CSV时的外键约束报错，有以下几种实用解决方案：

方案1：使用可延迟约束（推荐） #

如果你的外键约束支持延迟检查，可以在单个事务内完成所有表的数据导入，PostgreSQL会在事务提交时统一验证外键约束，避免导入过程中的实时检查报错。

1. 调整外键约束为可延迟（如果原本不是的话）：

   -- 修改表B的外键约束为可延迟
   ALTER TABLE b ALTER CONSTRAINT fk_b_a DEFERRABLE INITIALLY DEFERRED;
   -- 修改表A的外键约束为可延迟
   ALTER TABLE a ALTER CONSTRAINT fk_a_b DEFERRABLE INITIALLY DEFERRED;
   

   这里的fk_b_a和fk_a_b是你实际的外键约束名称，可以通过\d a和\d b命令查看。

2. 在事务中执行导入：

   BEGIN;
   -- 导入表A的数据
   COPY a FROM '/path/to/a_data.csv' WITH (FORMAT csv, HEADER true);
   -- 导入表B的数据
   COPY b FROM '/path/to/b_data.csv' WITH (FORMAT csv, HEADER true);
   COMMIT;
   

   事务提交时，PostgreSQL会检查所有外键关联是否合法，此时两张表的数据都已存在，不会触发约束错误。

方案2：临时禁用外键约束，导入后恢复 #

这种方法适合无法修改外键约束的场景，临时关闭外键的触发检查，导入完成后再重新启用并验证数据完整性。

1. 禁用外键触发器：

   -- 禁用表A的所有触发器（包含外键约束触发器）
   ALTER TABLE a DISABLE TRIGGER ALL;
   -- 禁用表B的所有触发器
   ALTER TABLE b DISABLE TRIGGER ALL;
   

2. 执行数据导入：

   COPY a FROM '/path/to/a_data.csv' WITH (FORMAT csv, HEADER true);
   COPY b FROM '/path/to/b_data.csv' WITH (FORMAT csv, HEADER true);
   

3. 恢复触发器并验证数据：

   -- 恢复表A的触发器
   ALTER TABLE a ENABLE TRIGGER ALL;
   -- 恢复表B的触发器
   ALTER TABLE b ENABLE TRIGGER ALL;
   -- 验证表A的外键完整性
   SELECT COUNT(*) FROM a WHERE b_id NOT IN (SELECT id FROM b);
   -- 验证表B的外键完整性
   SELECT COUNT(*) FROM b WHERE a_id NOT IN (SELECT id FROM a);
   

   如果验证结果为0，说明数据关联合法；若不为0，需要修正对应的数据后再重新检查。

方案3：分阶段导入，补全外键关联 #

如果CSV数据允许临时缺失外键字段，可以分步骤导入并补全关联：

1. 修改表结构允许外键字段为NULL（如果原本不允许的话）：

   ALTER TABLE a ALTER COLUMN b_id DROP NOT NULL;
   ALTER TABLE b ALTER COLUMN a_id DROP NOT NULL;
   

2. 导入无外键关联的基础数据：

   -- 导入表A数据（b_id字段留空或填NULL）
   COPY a (id, col1, col2) FROM '/path/to/a_data.csv' WITH (FORMAT csv, HEADER true);
   -- 导入表B数据（a_id字段填写表A已存在的id）
   COPY b (id, a_id, col3) FROM '/path/to/b_data.csv' WITH (FORMAT csv, HEADER true);
   

3. 补全表A的外键关联：

   UPDATE a SET b_id = (SELECT id FROM b WHERE b.a_id = a.id);
   

4. 恢复外键字段的NOT NULL约束（如果需要的话）：

   ALTER TABLE a ALTER COLUMN b_id SET NOT NULL;
   ALTER TABLE b ALTER COLUMN a_id SET NOT NULL;
   

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