PostgreSQL字符串转浮点后无法筛选浮点列问题咨询

环境 #

PostgreSQL 13.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit

问题场景 #

表my_table的datadec列是VARCHAR类型，存储了布尔值（true、false）、整数、浮点数、文本等多种数据。需要提取该列值为浮点数且大于28的行，编写的查询语句如下：

with t1 as 
(
    select datadec::float as value
    from my_table
    where 'HighLimitSummerSetpoint' = any(o.tags)
)
select * from t1 where value > 28

执行时触发错误：SQL Error [22P02]: ERROR: invalid input syntax for type double precision: "true"，但单独执行子查询t1却能正常返回float类型的结果。

原因分析 #

PostgreSQL的查询优化器会根据整体查询逻辑重写执行计划，不会严格按照CTE的定义顺序执行。单独运行子查询时，可能当前数据中符合过滤条件的行刚好没有无法转换为float的值（比如true），或者优化器选择了先过滤再转换；但当结合外层的value > 28条件后，优化器会将比较条件下推到子查询阶段，尝试对所有符合'HighLimitSummerSetpoint' = any(o.tags)的行直接执行datadec::float转换，包括那些布尔值、文本等无法转换的内容，因此触发转换报错。

解决方案 #

需要先筛选出能转换为浮点数的行，再进行类型转换和数值比较，以下两种方案可选：

方案1：使用safe_cast（PostgreSQL 12+推荐） #

safe_cast会在转换失败时返回null，避免报错：

with t1 as 
(
    select safe_cast(datadec as float) as value
    from my_table
    where 'HighLimitSummerSetpoint' = any(o.tags)
)
select * from t1 where value > 28 and value is not null

方案2：正则表达式预校验 #

通过正则匹配浮点数格式，提前过滤无法转换的行：

with t1 as 
(
    select datadec::float as value
    from my_table
    where 'HighLimitSummerSetpoint' = any(o.tags)
      and datadec ~ '^-?\d+(\.\d+)?$' -- 匹配整数或普通浮点数
)
select * from t1 where value > 28

如果需要支持科学计数法格式，可将正则调整为：'^-?\d+(\.\d+)?([eE][+-]?\d+)?$'

内容的提问来源于stack exchange，提问作者R. Bourgeon