PostgreSQL使用\COPY导入CSV时0000-00-00时间格式报错解决咨询

我有一个bash脚本，用于将MySQL导出的.csv文件导入PostgreSQL 11.16的backup_nb表，执行命令如下：

PGPASSWORD="${DATABASE_PASSWORD}" psql -h "${DATABASE_HOST}" -U "${DATABASE_USER}" -p "${DATABASE_PORT}" -d "${DATABASE_NAME}" -c "\COPY backup_nb FROM '$MYPATH\nb.csv' WITH DELIMITER ';' CSV HEADER"

但遇到错误：

ERROR: date/time field value out of range: "0000-00-00 00:00:00"
CONTEXT: COPY backup_nb , line 948, column sscomp: "0000-00-00 00:00:00"

表结构为：

CREATE TABLE backup_nb
(
    id integer ,
    server character varying(500) COLLATE pg_catalog."default" NOT NULL,
    sscomp timestamp without time zone
)

已知nullif('0000-00-00 00:00:00','0000-00-00 00:00:00')::timestamp可以处理该问题，但不知道如何在\COPY命令中实现，求可行方案。

方法1：在\COPY中指定列转换逻辑 #

\COPY支持对导入的列直接应用转换函数，只需明确指定列名并添加处理规则：

PGPASSWORD="${DATABASE_PASSWORD}" psql -h "${DATABASE_HOST}" -U "${DATABASE_USER}" -p "${DATABASE_PORT}" -d "${DATABASE_NAME}" -c "\COPY backup_nb(id, server, sscomp) FROM '$MYPATH/nb.csv' WITH DELIMITER ';' CSV HEADER (sscomp = nullif(sscomp, '0000-00-00 00:00:00')::timestamp without time zone)"

注意：路径中的\需根据操作系统调整——Windows用\，Linux/macOS用/。该命令会将CSV中sscomp列的0000-00-00 00:00:00转换为NULL，符合PostgreSQL时间类型的要求。

方法2：临时调整PostgreSQL参数允许无效时间 #

如果不想修改导入命令，可以临时开启PostgreSQL的zero_dates参数，让系统自动将0000-00-00这类无效时间转为NULL：

1. 先执行参数设置（仅当前会话生效）：

SET zero_dates = on;

2. 执行原有的\COPY命令完成导入
3. 导入结束后恢复默认配置：

SET zero_dates = off;

方法3：预处理CSV文件 #

直接修改CSV中的无效时间值为空，适合一次性导入场景：

sed -i 's/0000-00-00 00:00:00//g' "$MYPATH/nb.csv"

修改后执行原\COPY命令即可，PostgreSQL会将空字段识别为NULL（你的表中sscomp无NOT NULL约束，无需额外调整）。

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