HomeAssistant日志SQL查询报错22P02：numeric类型语法异常求助

问题根源 #

1. 查询优化器执行顺序问题：PostgreSQL的优化器可能调整WHERE子句中表达式的执行顺序，导致还未过滤掉无效记录就执行to_number转换，触发语法错误。
2. 正则表达式不严谨：你使用的trim(ste.state) ~ '[0-9]'仅检查字符串是否包含数字，而非是否为合法数值格式（比如带空格、字母的含数字字符串也会匹配），这类字符串进入then分支后，to_number转换必然失败。
3. 格式字符串不支持负号：格式符'99999999999D99'未包含负号标识，当else分支返回'-1'时，转换也会报错（如果存在符合else条件的记录）。

解决办法 #

方案1：修正正则与格式字符串，调整查询结构

通过子查询先完成所有数值转换，再在外层筛选异常值，避免优化器提前执行转换操作：

select state, st
from (
    select  ste.state
    ,       to_number(
                case 
                    -- 匹配整数、小数、带负号的合法数值
                    when trim(ste.state) ~ '^-?[0-9]+(\.[0-9]+)?$' 
                    then trim(ste.state) 
                    else '-1' 
                end,
                -- 添加S支持负号转换
                '99999999999D99S'
            ) st
    from    states_meta sma
    inner join states ste 
            on ste.metadata_id = sma.metadata_id
    where   sma.entity_id like 'sensor.dimmer_%_electric_consumed_w'
    and     ste.state is not null
    and     trim(ste.state) <> ''
) transformed
where st > 600;

方案2：使用PostgreSQL 12+的容错转换（版本支持时可用）

PostgreSQL 12及以上版本支持to_number的容错参数，转换失败时返回NULL，结合过滤条件即可：

select  ste.state
,       to_number(trim(ste.state), '99999999999D99S', 'TRY') st
from    states_meta sma
inner join states ste 
        on ste.metadata_id = sma.metadata_id
where   sma.entity_id like 'sensor.dimmer_%_electric_consumed_w'
and     ste.state is not null
and     trim(ste.state) <> ''
and     to_number(trim(ste.state), '99999999999D99S', 'TRY') > 600;

为什么中间表可以正常运行？ #

因为中间表已经存储了转换后的数值结果，筛选时直接读取数值，不需要再次执行to_number转换，自然不会触发转换错误。

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