SQL查询空表触发列不存在报错 如何实现无数据时返回0行

现有查询语句如下：

select
   max(case when measure_name = 'consumption' then measure_value::double end) 
consumption, 
   max(case when measure_name = 'generation' then measure_value::double end) 
generation,
   max(case when measure_name = 'self_coverage' then measure_value::double end) 
self_coverage, 
   time, 
   point_delivery_number 
from "test_database"."raw" 
GROUP BY time, point_delivery_number 
ORDER BY time DESC

该查询在目标表有数据时可正常运行，表为空时会抛出报错：

Column 'measure_value::double' does not exist

需要实现效果：表内无数据时查询直接返回0行结果，不触发上述报错。

这个报错是因为空表状态下，查询引擎解析阶段没法正确识别measure_value::double的类型转换逻辑，直接判定列不存在。可以用以下两种方式处理：

• 方法1：加前置数据存在性判断
  用EXISTS子句判断表内是否有数据，空表时直接返回空结果，不执行后续带类型转换的聚合逻辑，写法如下：

  SELECT * FROM (
    select
       max(case when measure_name = 'consumption' then measure_value::double end) consumption, 
       max(case when measure_name = 'generation' then measure_value::double end) generation,
       max(case when measure_name = 'self_coverage' then measure_value::double end) self_coverage, 
       time, 
       point_delivery_number 
    from "test_database"."raw" 
    GROUP BY time, point_delivery_number 
  ) res
  WHERE EXISTS (SELECT 1 FROM "test_database"."raw")
  ORDER BY time DESC
  

  这种写法完全保留原有查询逻辑，不需要调整字段转换规则，兼容性最好。
• 方法2：替换类型转换写法
  把::double的简写类型转换换成标准CAST函数写法，大部分引擎对标准CAST的空表解析兼容性更好，不会抛出列不存在错误：

  select
     max(case when measure_name = 'consumption' then CAST(measure_value AS double) end) consumption, 
     max(case when measure_name = 'generation' then CAST(measure_value AS double) end) generation,
     max(case when measure_name = 'self_coverage' then CAST(measure_value AS double) end) self_coverage, 
     time, 
     point_delivery_number 
  from "test_database"."raw" 
  GROUP BY time, point_delivery_number 
  ORDER BY time DESC
  

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