sql - postgreSQL TO_NUMBER 函数因附加 WHERE 限制而失败？

Question

我在 postgres 中有一个表，其中包含已解析的地址信息。当我运行以下查询时，我得到了正确的结果:

select count(*) from address_table
  where (mod(to_number(to_address_left, '99999999'), 2) = 0 
         and to_address_left <> ' ')

但是当我向它添加一个 OR 时......

select count(*) from address_table
  where (mod(to_number(to_address_left, '99999999'), 2) = 0 
         and to_address_left <> ' ')
     or (from_address_left <> ' ')

我收到以下错误...

ERROR:  invalid input syntax for type numeric: " "
SQL state: 22P02

我认为这是与 TO_NUMBER 函数相关的错误。我知道其中一些记录中有空格(有意)，这就是包含“and to_address_left <> ' '”的原因。但我不明白为什么添加引用完全不同字段的 OR 语句会导致其他独立的 TO_NUMBER 函数生成错误。

此代码在 Oracle SQL 中运行良好。为什么它在这里不起作用？

Answer 1

SQL 是一种声明式语言。查询规划器可以按照它喜欢的任何顺序自由地评估表达式，只要它产生正确的结果。所以，如果你添加一个 OR子句，它可能会生成一个计算 to_number 的查询计划首先，to_address_left <> ' '第二。对于 to_address_left 中带有空格的行，这会引发异常。 .

解决此问题的一致方法是确保 to_number评估从不引发异常。看起来像 to_number对于任何包含数字的字符串都会成功，即使是 'b 1 d' , 所以你可以在 case 中测试它:

to_number(case when str_col ~ '[0-9]' then str_col end, '99999999')

这样，Postgres 将评估 to_number不引发异常，即使该行后来被 where 的其他部分抛出条款。

依赖于 Postgres 实现公用表表达式的替代方法(目前所有 Postgres 版本都这样做，但它不在 SQL 标准中):

with    CTE as
        (
        select  *
        from    YourTable
        where   str_col not like '% %'
        )
select  *
from    CTE
-- Line below below guaranteed not to run for rows with a space in str_col
where   to_number(str_col) > 42