Postgres列顺序不一致致pg_restore导入失败的解决方法

问题原因 #

生产库与本地库中，对应Django MoneyField的两个字段（numeric类型折扣字段、varchar类型货币字段）列顺序不一致。使用--inserts参数导出的INSERT语句会严格按照生产库的列顺序生成，导入本地库时就会把货币值"AUD"错误插入到numeric类型字段中，触发invalid input syntax for type numeric报错。

解决方案 #

1. 修改pg_dump参数，生成带列名的INSERT语句（推荐） #

这是最简便的解决方式，无需调整表结构。在原有pg_dump命令中添加--column-inserts参数，该参数会强制生成带显式列名的INSERT语句，PostgreSQL会按列名匹配数据，不再受表列顺序影响。

修改后的命令示例：

pg_dump --dbname="XXX" --format=t --file="XXX/DataGrip Backup/{timestamp}" --column-inserts --ignore-triggers

保留你已有的--ignore-triggers参数，用新生成的tar备份文件执行pg_restore即可正常导入。

2. 调整本地表的列顺序与生产库一致 #

如果不想修改导出参数，可以手动调整本地目标表的列顺序，和生产库完全对齐：

• 先备份本地表数据（防止操作失误丢失数据）
• 执行ALTER TABLE语句调整列位置，假设生产库中折扣字段在前、货币字段在后，本地库顺序相反，示例语句：

ALTER TABLE your_target_table ALTER COLUMN coupon_discount_currency SET AFTER coupon_discount;

调整完成后，使用原有备份文件重新导入即可。

3. 导出为SQL文件手动修正（应急方案） #

若上述两种方式暂时无法执行，可导出为纯SQL文件手动修改目标表的INSERT语句：

1. 导出SQL格式备份：

pg_dump --dbname="XXX" --format=p --file="backup.sql" --inserts --ignore-triggers

2. 打开SQL文件，找到目标表的INSERT语句，添加显式列名列表，例如：
   将INSERT INTO your_target_table VALUES (123.45, 'AUD')
   修改为INSERT INTO your_target_table (coupon_discount, coupon_discount_currency) VALUES (123.45, 'AUD')
3. 通过psql或DataGrip执行修改后的SQL文件完成导入。

是否需要更换导出方式？ #

不需要更换tar格式备份。tar格式具备压缩、便于管理的优势，只需调整pg_dump的参数（如添加--column-inserts）即可解决列顺序问题，无需改变备份格式。

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