PostgreSQL导入TXT报错：日期值'00000000'越界，如何替换为NULL？

问题背景 #

导入竖线(|)分隔的TXT文件时，因日期字段使用"00000000"表示空值，不符合PostgreSQL日期格式，触发如下错误：

SQL Error [22008]: ERROR: date/time field value out of range: "00000000"
Where: COPY drugdata, line 1, column desdtec: "00000000"

执行的导入语句：

copy drugdata  from '/home/manishl/drugdbfile/drugdata.txt' with delimiter as '|';

样本数据（drugdata.txt）：

00002091155|TEMPO SMART BUTTON|00000000|00000000
00002094755|TEMPO REFILL KIT|00000000|00000000

目标表结构：

CREATE TABLE public.drugdata(
ndc varchar(11) NOT NULL,
desc varchar(1000) NULL,
startdate date NULL,
expirydate date NULL);

解决方案 #

可以通过两种方式将"00000000"替换为NULL完成导入：

方法一：直接修改COPY命令（推荐） #

利用PostgreSQL COPY命令的NULL AS参数，指定输入中代表NULL的字符串为"00000000"，导入时自动转换：

COPY drugdata FROM '/home/manishl/drugdbfile/drugdata.txt' 
WITH DELIMITER '|' NULL AS '00000000';

方法二：通过临时表中转 #

如果方法一因权限或版本限制无法使用，可先将数据导入临时文本表，再转换空值插入正式表：

1. 创建临时表（所有字段为文本类型）：

CREATE TEMP TABLE temp_drugdata (
    ndc varchar(11) NOT NULL,
    desc varchar(1000) NULL,
    startdate text NULL,
    expirydate text NULL
);

2. 导入数据到临时表：

COPY temp_drugdata FROM '/home/manishl/drugdbfile/drugdata.txt' WITH DELIMITER '|';

3. 转换空值并插入正式表：

INSERT INTO drugdata (ndc, desc, startdate, expirydate)
SELECT 
    ndc,
    desc,
    CASE WHEN startdate = '00000000' THEN NULL ELSE startdate::date END,
    CASE WHEN expirydate = '00000000' THEN NULL ELSE expirydate::date END
FROM temp_drugdata;

4. 清理临时表：

DROP TABLE temp_drugdata;

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