Postgres中JSON提取的YYYY-MM-DD字符串转date类型报错如何解决

报错的核心原因是你提取到的日期字符串里包含不可见的Unicode控制字符（最常见为左到右标记U+200E），这类字符不会在文本展示中显示，但会导致日期类型转换识别失败。你看到的"‎2020-‎12-‎09"实际字符长度大于合法YYYY-MM-DD格式的10位，可以用以下语句验证：

SELECT octet_length(data->>'closeDate') FROM 你的表名 LIMIT 1;

如果返回结果大于10即可确认存在不可见字符。

可用解决方案 #

• 方案1（通用首选）：用正则清除所有非数字、非横杠的字符后再做类型转换

(regexp_replace(data->>'closeDate', '[^0-9-]', '', 'g'))::date AS PortalCloseDate

• 方案2（精准替换）：如果确认仅存在U+200E左到右标记，可以单独替换该字符

(replace(data->>'closeDate', U&'\200E', ''))::date AS PortalCloseDate

• 方案3（强兼容兜底）：清除所有非数字字符后，按纯数字日期格式转换，适配更多不规则格式

to_date(regexp_replace(data->>'closeDate', '[^0-9]', '', 'g'), 'YYYYMMDD') AS PortalCloseDate

长期优化建议 #

建议在数据写入JSON字段前，先对日期类字段做清洗，去除不可见控制字符，避免后续查询过程中反复做转换处理。

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