Psycopg2使用COPY_FROM导入CSV数据时无法保持行顺序问题求助

核心原因 #

PostgreSQL的普通表是无序列堆存储结构，数据库本身没有义务保证行的物理存储顺序和插入顺序一致，你平时不带ORDER BY查询得到的行顺序是不可靠的，会受扫描路径、表碎片、并发操作等很多因素影响，并不是COPY_FROM写入的时候打乱了顺序。

错乱的具体诱因 #

1. PostgreSQL 14及以上版本支持并行COPY_FROM操作，并行导入时多个线程同时读取写入，自然会打乱行的插入顺序
2. 如果目标表存在索引、约束、触发器，也会影响行的实际存储排布
3. 全表扫描时的返回顺序完全由执行规划器决定，和写入顺序没有必然关联

解决方案 #

如果必须保留CSV的原始行顺序，主动添加顺序标识列即可：

1. 调整表结构，新增一个自增序列字段，例如：

ALTER TABLE 你的表名 ADD COLUMN seq_id serial PRIMARY KEY;

2. 重新执行COPY_FROM导入，导入时不需要给seq_id列赋值，PostgreSQL会自动按写入顺序生成连续递增的序列值
3. 后续查询时显式加上排序规则，就能得到和CSV原顺序完全一致的结果：

SELECT * FROM 你的表名 ORDER BY seq_id ASC;

内容的提问来源于stack exchange，提问作者Salvin D'souza