使用psycopg2向PostgreSQL插入数据时，空日期/时间设为NULL报错

问题原因 #

错误提示里的输入是" "（多个空格），你当前用的NULLIF(%s,'')仅会把空字符串转为NULL，但对于包含空格的字符串，这个函数不会触发转换。PostgreSQL尝试把空格字符串强制转为date/time类型，因此抛出格式错误。

解决方案 #

修改INSERT语句中的NULLIF逻辑，先去除输入字符串的首尾空格，再判断是否为空：

INSERT INTO userbasic
(name, create_date, owner_id, adsp, special,
 oper, revoke, grpacc, pwd_interval, pwd_date,
 programmer, defgrp_id, lastjob_time, lastjob_date, install_data)
VALUES 
(%s, NULLIF(TRIM(%s), '')::date, %s, %s, %s, 
%s, %s, %s, %s, NULLIF(TRIM(%s), '')::date, 
%s, %s,NULLIF(TRIM(%s), '')::time, NULLIF(TRIM(%s), '')::date, %s)

特殊情况处理 #

如果文件中的空值还可能包含制表符、换行符等其他空白字符，可以用正则替换清除所有空白内容：

NULLIF(REGEXP_REPLACE(%s, '^\s+$', '', 'g'), '')::date

这种写法能把任意空白字符组合转换成空字符串，再通过NULLIF转为NULL。

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