PostgreSQL：从jsonb查询时处理timestamp转换的无效值

直接用::timestamp强制转换遇到无效日期格式或NULL时会中断查询，你可以用PostgreSQL提供的容错转换函数处理，以下是两种适配不同版本的方案：

方案一：使用try_cast（PostgreSQL 14+） #

try_cast会尝试转换值，失败时返回NULL，不会终止查询，写法简洁：

Select
    try_cast((jsonb_path_query(AnchorNode, '$.TestDate')#>> '{}') as timestamp) as TestDate,
    try_cast((jsonb_path_query(AnchorNode, '$.CreateDate')#>> '{}') as timestamp) as CreateDate,
    -- 其余6-7个日期字段按同样格式添加
From (
      Select 
          jsonb_path_query_first(payload, '$.node1[*].node2[*]') as AnchorNode
      From TestTable
     ) subq1

方案二：使用to_timestamp的错误处理子句（PostgreSQL 12+） #

如果你的PostgreSQL版本低于14，可用to_timestamp配合on conversion error子句，需指定日期格式（ISO标准格式可用using iso8601简化）：

Select
    -- 适配ISO格式日期（如'2023-10-01T14:30:00'）
    to_timestamp(jsonb_path_query(AnchorNode, '$.TestDate')#>> '{}') using iso8601 on conversion error null as TestDate,
    -- 适配自定义格式（如'2023-10-01 14:30:00'）
    to_timestamp(jsonb_path_query(AnchorNode, '$.CreateDate')#>> '{}', 'YYYY-MM-DD HH24:MI:SS') on conversion error null as CreateDate,
    -- 其余日期字段按需调整格式串
From (
      Select 
          jsonb_path_query_first(payload, '$.node1[*].node2[*]') as AnchorNode
      From TestTable
     ) subq1

注意事项 #

• 两种方案都会自动处理NULL值：当JSON中对应字段为NULL或不存在时，转换结果为NULL，不会报错。
• 使用to_timestamp时，格式串必须和JSON中存储的日期格式匹配，否则会触发错误转换逻辑返回NULL。

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