INSERT语句解析JSON时日期类型不匹配问题求助

错误原因是PostgreSQL无法将空字符串""解析为date类型。虽然目标表的effective_from_date和effective_to_date字段允许NULL，但json_populate_recordset会先尝试把JSON中的空字符串直接转换为date类型，这一步就会抛出语法错误——NULLIF是在转换之后才执行的，根本没机会生效。

通过先将JSON中的日期字段提取为文本、处理空值后再转换为date类型的方式修复，修改后的INSERT...SELECT语句如下：

INSERT INTO user_type (cli_nk, effective_from_date, effective_to_date, user_dml_type_code, user_fix_desc)
SELECT
    NULLIF(p.cli_nk::text, ''),
    NULLIF(p.effective_from_date::text, '')::date,
    NULLIF(p.effective_to_date::text, '')::date,
    NULLIF(p.user_dml_type_code::text, ''),
    NULLIF(p.user_fix_desc::text, '')
FROM json_import l
    CROSS JOIN LATERAL json_populate_recordset(NULL::user_type, doc) AS p;

可选替代方案：手动指定字段类型解析 #

如果不想依赖目标表结构，也可以用json_to_recordset明确将日期字段解析为text后再处理：

INSERT INTO user_type (cli_nk, effective_from_date, effective_to_date, user_dml_type_code, user_fix_desc)
SELECT
    NULLIF(cli_nk, ''),
    NULLIF(effective_from_date, '')::date,
    NULLIF(effective_to_date, '')::date,
    NULLIF(user_dml_type_code, ''),
    NULLIF(user_fix_desc, '')
FROM json_import l
    CROSS JOIN LATERAL json_to_recordset(doc) AS x(
        cli_nk text,
        effective_from_date text,
        effective_to_date text,
        user_dml_type_code bpchar(1),
        user_fix_desc text
    );

修改后，JSON中的空字符串会先被NULLIF转换为NULL，再转换为date类型（NULL可正常存入允许NULL的date字段）；有效的日期字符串则会被正确解析为date类型，彻底避免语法错误。

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