PostgreSQL日期格式不识别错误：日期运算与比较问题排查

错误原因 #

报错是因为TO_DATE(v.payment_date, 'YYYYMMDD')无法将部分v.payment_date的值转换为日期，大概率存在以下情况：

• v.payment_date为NULL
• 值的长度不是8位（不符合YYYYMMDD格式）
• 值包含非数字字符

解决步骤 #

1. 排查无效数据 #

运行以下SQL找出格式异常的payment_date记录，定位问题根源：

SELECT doc.document_id, doc.value AS payment_date
FROM go_appr_doc_variables doc
WHERE doc.name='paymentDate'
AND (doc.value IS NULL OR length(doc.value) != 8 OR doc.value !~ '^\d{8}$');

2. 修正查询逻辑 #

修改原SQL，先过滤无效数据，同时优化日期比较逻辑（用日期类型直接对比，比字符串转换更可靠）：

SELECT
  v.accounting_date,
  v.payment_date
FROM 
  (SELECT doc.document_id, 
    max(CASE WHEN doc.name='accountingDate' THEN doc.value END) AS accounting_date,
    max(CASE WHEN doc.name='paymentDate' THEN doc.value END) AS payment_date
  FROM go_appr_doc_variables doc
  GROUP BY doc.document_id) v
WHERE 
  -- 过滤无效格式的payment_date
  v.payment_date IS NOT NULL 
  AND length(v.payment_date) = 8 
  AND v.payment_date ~ '^\d{8}$'
  -- 直接用日期类型做比较，避免字符串转换的格式问题
  AND TO_DATE(v.payment_date, 'YYYYMMDD') + INTERVAL '8 days' >= CURRENT_DATE;

补充说明 #

如果业务上需要保留字符串格式的比较，也可以在确保数据有效的前提下继续使用原转换方式，但日期类型的对比在性能和可靠性上更优。

内容的提问来源于stack exchange，提问作者송재헌