PostgreSQL查询报错：time类型转换失败，如何修正该查询？

报错原因 #

1. 类型转换语法错误：查询中session->>'start_time'::time的写法有误，::time被错误绑定到JSON键名'start_time'上，相当于尝试将字符串'start_time'转换为time类型，这显然不符合时间格式规范，直接触发invalid input syntax for type time错误。
2. 子查询用法错误：WHERE子句中直接放置返回行集的子查询是非法的，WHERE需要布尔值判断，这种写法会导致语法逻辑错误。

修正后的查询语句 #

SELECT *
FROM programs
WHERE EXISTS (
    SELECT 1
    FROM unnest(sessions) AS session
    WHERE session->>'day' = :day
      AND :currentDate::time BETWEEN 
          ((session->>'start_time')::time - (entry_time_range || ' minutes')::interval)::time
          AND 
          ((session->>'start_time')::time + (entry_time_range || ' minutes')::interval)::time
)              
AND (filter IS NULL OR filter LIKE :schedule)
AND :currentDate BETWEEN startline AND deadline       
AND facility_id = :facility_id;

关键修改点 #

• 改用EXISTS子查询：用EXISTS (SELECT 1 ...)替代原有子查询写法，EXISTS专门用于判断子查询是否存在匹配行，返回布尔值，完全符合WHERE子句的逻辑要求。
• 修正时间转换逻辑：将session->>'start_time'::time调整为(session->>'start_time')::time，确保先通过->>提取JSON字段中的时间字符串值，再将其转换为time类型，避免错误转换键名。
• 简化时间转换步骤：原语句中to_timestamp(session->>'start_time', 'HH24:MI:SS')::time可简化为(session->>'start_time')::time，因为你的时间字符串是标准HH24:MI:SS格式，PostgreSQL可直接完成转换，无需通过to_timestamp中转。

额外优化建议 #

如果entry_time_range是数字类型（而非字符串），可将(entry_time_range || ' minutes')::interval替换为entry_time_range * interval '1 minute'，这种写法更高效，也能避免字符串拼接可能带来的类型问题。

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