PostgreSQL中添加WHERE条件后TO_NUMBER函数执行失败？

哎，这个坑我之前踩过！核心问题在于PostgreSQL处理OR条件时，不会保证表达式的求值顺序——哪怕某一行明明满足右边的from_address_left <> ' '，数据库可能还是会先去计算左边的mod(to_number(to_address_left, '99999999'), 2) = 0。如果这时候to_address_left是空字符串或者不是合法的数字格式，to_number直接就抛出"invalid input syntax"错误了。

原来的查询之所以正常，是因为to_address_left <> ' '先过滤掉了空值，数据库只会对非空的to_address_left执行数字转换；但加了OR之后，这个过滤逻辑不再是强制前置的了。

下面给你几个可行的解决方案：

方案1：用CASE表达式严格控制求值顺序 #

把左边的条件用CASE包裹，确保只有当to_address_left非空且是纯数字时，才执行mod计算：

select count(*) 
from address_table 
where 
  (
    CASE 
      WHEN to_address_left <> ' ' AND to_address_left ~ '^[0-9]+$' 
      THEN mod(to_number(to_address_left, '99999999'), 2) = 0 
      ELSE false 
    END
  ) 
  OR (from_address_left <> ' ');

这里额外加了to_address_left ~ '^[0-9]+$'正则判断，避免非数字字符串触发转换错误。

方案2：使用TRY_CAST（PostgreSQL 12+适用） #

如果你的PostgreSQL版本是12或以上，可以用TRY_CAST替代to_number，它在转换失败时会返回NULL而非报错：

select count(*) 
from address_table 
where 
  (mod(TRY_CAST(to_address_left AS integer), 2) = 0 AND to_address_left <> ' ') 
  OR (from_address_left <> ' ');

注意：如果to_address_left是超出integer范围的大数字，记得换成bigint类型。

方案3：拆分查询用UNION ALL合并结果 #

把两个条件的查询分开执行，再合并计数，这样两个分支的条件不会互相干扰：

select sum(cnt) as total_count
from (
  select count(*) as cnt from address_table where mod(to_number(to_address_left, '99999999'), 2) = 0 and to_address_left <> ' '
  union all
  select count(*) as cnt from address_table where from_address_left <> ' ' and (to_address_left = ' ' OR mod(to_number(to_address_left, '99999999'), 2) != 0)
) t;

这个方法代码稍长，但能严格保证每个分支都不会触发无效转换，适合版本较低的PostgreSQL环境。

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