PostgreSQL中TO_TIMESTAMP转换报错及分步查询正常的问题求助

问题描述 #

在执行一段复杂的PostgreSQL查询时，只要在末尾添加tms > TO_TIMESTAMP('2023-05-18T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')过滤条件，就会报错：ERROR: invalid value "test" for "YYYY"；但如果先把不带过滤条件的查询结果存入临时表，再对临时表执行相同的过滤条件，就能正常运行。

报错的完整查询代码：

select * from
     (select i.uuid as t1uid,
             m3.short_id as short_id_start,
             m.text_value as original_date,
             TO_TIMESTAMP( trim ( substring(m.text_value, 1, 19)), 'YYYY-MM-DD"T"HH24:MI:SS' ) as tms 
      from item i
      inner join metadatavalue m on i.uuid = m.dspace_object_id
      inner join metadatafieldregistry m2 on m.metadata_field_id = m2.metadata_field_id
      inner join metadataschemaregistry m3 on m2.metadata_schema_id = m3.metadata_schema_id
      and m3.short_id ='interaction'
      and m2."element" = 'startts') t1
   join
     (select i.uuid as t2uid,
             m3.short_id as short_id_fail,
             m.text_value as success_status
      from item i
      inner join metadatavalue m on i.uuid = m.dspace_object_id
      inner join metadatafieldregistry m2 on m.metadata_field_id = m2.metadata_field_id
      inner join metadataschemaregistry m3 on m2.metadata_schema_id = m3.metadata_schema_id
      and m3.short_id ='interaction'
      and m2."element" = 'status'
      and m.text_value = 'SUCCESS') t2 on t1.t1uid = t2.t2uid
   join
     (select i.uuid as t3uid,
             m3.short_id as short_id_start_endt,
             TO_TIMESTAMP( trim ( substring(m.text_value, 1, 19)), 'YYYY-MM-DD"T"HH24:MI:SS' )as enddate
      from item i
      inner join metadatavalue m on i.uuid = m.dspace_object_id
      inner join metadatafieldregistry m2 on m.metadata_field_id = m2.metadata_field_id
      inner join metadataschemaregistry m3 on m2.metadata_schema_id = m3.metadata_schema_id
      and m3.short_id ='interaction'
      and m2."element" = 'endts') t3 on t3.t3uid = t2.t2uid
   left join
     (select m.text_value as "Protocollo",
             i.uuid as t4uid
      from item i
      inner join metadatavalue m on i.uuid = m.dspace_object_id
      inner join metadatafieldregistry m2 on m.metadata_field_id = m2.metadata_field_id
      inner join metadataschemaregistry m3 on m2.metadata_schema_id = m3.metadata_schema_id
      where m3.short_id ='interaction'
        and m2."element" = 'protocol' ) t4 on t4.t4uid = t3.t3uid
   left join
     (select i.uuid as t5uid,
             m2.qualifier as qualifier_sender,
             m.text_value as "Sender"
      from item i
      inner join metadatavalue m on i.uuid = m.dspace_object_id
      inner join metadatafieldregistry m2 on m.metadata_field_id = m2.metadata_field_id
      inner join metadataschemaregistry m3 on m2.metadata_schema_id = m3.metadata_schema_id
      where m3.short_id ='interaction'
        and m2."element" = 'application'
        and m2.qualifier in ('sender') ) as t5 on t1.t1uid = t5.t5uid
   left join
     (select i.uuid as t6uid,
             m2.qualifier as qualifier_receiver,
             m.text_value as "Receiver"
      from item i
      inner join metadatavalue m on i.uuid = m.dspace_object_id
      inner join metadatafieldregistry m2 on m.metadata_field_id = m2.metadata_field_id
      inner join metadataschemaregistry m3 on m2.metadata_schema_id = m3.metadata_schema_id
      where m3.short_id ='interaction'
        and m2."element" = 'application'
        and m2.qualifier in ('receiver') ) 
        as t6 on t1.t1uid = t6.t6uid
        where tms > TO_TIMESTAMP('2023-05-18T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') 

原因分析 #

问题出在PostgreSQL的查询优化器执行顺序上：当你在外层添加tms过滤条件时，优化器可能会将这个过滤逻辑提前到子查询阶段执行。但子查询中的metadatavalue表存在不符合时间格式的text_value（比如值为"test"的记录），提前执行转换就会触发格式错误。

而先将查询结果存入表的方式，相当于先完成了所有合法数据的转换（非法数据要么在转换时被自动处理为NULL，要么因为转换失败被排除），后续过滤时只针对已转换好的时间字段，自然不会报错。

解决方案 #

这里提供三种可行的解决方法，按需选择：

1. 子查询中提前过滤非法格式数据 #

在生成tms字段的子查询里，用正则表达式过滤掉不符合时间格式的text_value，确保只有合法数据进入转换步骤：

select * from
     (select i.uuid as t1uid,
             m3.short_id as short_id_start,
             m.text_value as original_date,
             TO_TIMESTAMP( trim ( substring(m.text_value, 1, 19)), 'YYYY-MM-DD"T"HH24:MI:SS' ) as tms 
      from item i
      inner join metadatavalue m on i.uuid = m.dspace_object_id
      inner join metadatafieldregistry m2 on m.metadata_field_id = m2.metadata_field_id
      inner join metadataschemaregistry m3 on m2.metadata_schema_id = m3.metadata_schema_id
      and m3.short_id ='interaction'
      and m2."element" = 'startts'
      -- 添加正则过滤，只保留符合YYYY-MM-DDTHH:MM:SS格式的记录
      and m.text_value ~ '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}'::text) t1
   -- 后续的join和过滤逻辑保持不变...
   where tms > TO_TIMESTAMP('2023-05-18T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')

2. 使用容错转换函数（PostgreSQL 12+） #

PostgreSQL 12及以上版本支持TO_TIMESTAMP的容错语法，转换失败时返回NULL，不会抛出错误：

-- 将子查询中的tms转换语句修改为：
TO_TIMESTAMP(trim(substring(m.text_value, 1, 19)), 'YYYY-MM-DD"T"HH24:MI:SS') ON CONVERSION ERROR NULL as tms

这样非法格式的text_value会被转换为NULL，外层的tms > ...过滤条件会自动排除这些NULL值，不会报错。

3. 强制子查询物化 #

通过MATERIALIZED关键字强制子查询先执行并生成临时结果，避免优化器提前执行过滤逻辑：

select * from
     (select i.uuid as t1uid,
             m3.short_id as short_id_start,
             m.text_value as original_date,
             TO_TIMESTAMP( trim ( substring(m.text_value, 1, 19)), 'YYYY-MM-DD"T"HH24:MI:SS' ) as tms 
      from item i
      inner join metadatavalue m on i.uuid = m.dspace_object_id
      inner join metadatafieldregistry m2 on m.metadata_field_id = m2.metadata_field_id
      inner join metadataschemaregistry m3 on m2.metadata_schema_id = m3.metadata_schema_id
      and m3.short_id ='interaction'
      and m2."element" = 'startts') t1 MATERIALIZED -- 添加MATERIALIZED关键字
   -- 后续逻辑不变...

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