PostgreSQL报错：YYYY格式化字段对应源字符串过短问题处理

该报错由TO_TIMESTAMP函数解析日期失败触发，你的SQL语句中PAYMENTDATE、IGBPAYMENTTRANSFERDATE两个字段转为字符串后，存在长度不足8位的脏数据，无法匹配YYYYMMDD的8位格式要求，其中YYYY需要4位字符，脏数据无法满足该长度要求。

• 首先定位不符合格式的脏数据，分别执行以下两条查询：
  排查PAYMENTDATE字段：

  SELECT PAYMENTDATE, LENGTH(PAYMENTDATE::VARCHAR) AS str_len 
  FROM KKMO.sap_transactions_view 
  WHERE PAYMENTDATE IS NOT NULL AND LENGTH(PAYMENTDATE::VARCHAR) != 8;
  

  排查IGBPAYMENTTRANSFERDATE字段：

  SELECT IGBPAYMENTTRANSFERDATE, LENGTH(IGBPAYMENTTRANSFERDATE::VARCHAR) AS str_len 
  FROM KKMO.sap_transactions_view 
  WHERE IGBPAYMENTTRANSFERDATE IS NOT NULL AND LENGTH(IGBPAYMENTTRANSFERDATE::VARCHAR) != 8;
  

• 确认脏数据类型：判断是无效值（如0、非法数字）还是格式不统一（如部分为6位YYMMDD格式）。

根据脏数据类型选择对应方案：

1. 脏数据为无效值，可直接过滤
   在日期转换前增加长度校验，仅处理符合8位长度的记录，修改原SQL的WHERE子句中日期判断部分：

   AND (
     (PAYMENTDATE IS NOT NULL AND LENGTH(PAYMENTDATE::VARCHAR) = 8 AND TO_TIMESTAMP(PAYMENTDATE::VARCHAR,'YYYYMMDD')::DATE = CURRENT_DATE)
     OR 
     (IGBPAYMENTTRANSFERDATE IS NOT NULL AND LENGTH(IGBPAYMENTTRANSFERDATE::VARCHAR) = 8 AND TO_TIMESTAMP(IGBPAYMENTTRANSFERDATE::VARCHAR,'YYYYMMDD')::DATE = CURRENT_DATE)
   )
   

2. 脏数据为6位YYMMDD格式，需要兼容
   增加分支判断适配两种格式：

   AND (
     CASE 
       WHEN PAYMENTDATE IS NOT NULL AND LENGTH(PAYMENTDATE::VARCHAR) = 8 THEN TO_TIMESTAMP(PAYMENTDATE::VARCHAR,'YYYYMMDD')::DATE
       WHEN PAYMENTDATE IS NOT NULL AND LENGTH(PAYMENTDATE::VARCHAR) = 6 THEN TO_TIMESTAMP(PAYMENTDATE::VARCHAR,'YYMMDD')::DATE
       ELSE NULL 
     END = CURRENT_DATE
     OR
     CASE 
       WHEN IGBPAYMENTTRANSFERDATE IS NOT NULL AND LENGTH(IGBPAYMENTTRANSFERDATE::VARCHAR) = 8 THEN TO_TIMESTAMP(IGBPAYMENTTRANSFERDATE::VARCHAR,'YYYYMMDD')::DATE
       WHEN IGBPAYMENTTRANSFERDATE IS NOT NULL AND LENGTH(IGBPAYMENTTRANSFERDATE::VARCHAR) = 6 THEN TO_TIMESTAMP(IGBPAYMENTTRANSFERDATE::VARCHAR,'YYMMDD')::DATE
       ELSE NULL 
     END = CURRENT_DATE
   )
   

3. 临时规避严格格式校验
   按照报错提示使用FM修饰符，放宽格式校验要求：

   AND (
     TO_DATE(PAYMENTDATE::VARCHAR, 'FMYYYYMMDD') = CURRENT_DATE
     OR TO_DATE(IGBPAYMENTTRANSFERDATE::VARCHAR, 'FMYYYYMMDD') = CURRENT_DATE
   )
   

如果使用PostgreSQL 12及以上版本，可替换为TO_TIMESTAMP_SAFE函数，转换失败时直接返回NULL，不会抛出异常

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