PostgreSQL中如何将含多格式的文本日期列转换为Date类型

你可以根据你的PostgreSQL版本选择以下任意一种方案实现转换，NULL值会自动保留无需额外处理：

方案1：基于字符串长度分支处理 #

两种日期格式的字符长度固定：YYYYMMDD为8位，YYYY-MM-DD为10位，直接按长度分支解析即可，性能最优：

SELECT 
  CASE
    WHEN LENGTH(event_date) = 8 THEN TO_DATE(event_date, 'YYYYMMDD')
    WHEN LENGTH(event_date) = 10 THEN TO_DATE(event_date, 'YYYY-MM-DD')
    ELSE NULL
  END AS event_date_parsed
FROM your_table;

方案2：统一格式后解析（代码更简洁） #

先删除所有非数字字符，将两种格式统一为YYYYMMDD后再解析，适配性更强：

SELECT TO_DATE(REGEXP_REPLACE(event_date, '[^0-9]', '', 'g'), 'YYYYMMDD') AS event_date_parsed
FROM your_table;

其中REGEXP_REPLACE的第四个参数'g'代表全局替换，会清除日期文本中所有横杠、空格等非数字字符。

方案3：带非法值容错的解析 #

如果你的表中可能存在不符合两种格式的脏数据，且不希望查询被报错中断：

• PostgreSQL 14及以上版本可以直接用内置的TO_DATE_OR_NULL函数：

SELECT 
  TO_DATE_OR_NULL(REGEXP_REPLACE(event_date, '[^0-9]', '', 'g'), 'YYYYMMDD') AS event_date_parsed
FROM your_table;

• 低版本PostgreSQL可以用正则先校验格式再解析：

SELECT 
  CASE
    WHEN event_date ~ '^[0-9]{8}$' THEN TO_DATE(event_date, 'YYYYMMDD')
    WHEN event_date ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' THEN TO_DATE(event_date, 'YYYY-MM-DD')
    ELSE NULL
  END AS event_date_parsed
FROM your_table;

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