PostgreSQL中字符串与NULL值比较的查询异常问题排查

这是PostgreSQL里处理NULL值时的典型陷阱！原因很简单：在SQL中，NULL和任何值（包括它自己）使用=比较的结果都是NULL，而非TRUE。所以当你的$3或$4参数是NULL时，lower(am.variant) = lower($3)这类条件永远不会匹配表中对应的NULL值。

修复思路 #

针对每个可能为NULL的参数，我们需要分情况判断：如果参数是NULL，就检查字段是否也是NULL；如果参数非NULL，再做大小写不敏感的匹配。

修正后的查询语句 #

把原来的条件替换成下面的逻辑：

SELECT count(*) 
FROM app.car_models am, app.business_entities be 
WHERE am.manufacturer=be.id 
  AND be.public_id=$1 
  AND (lower(am.name) = lower($2))
  -- 处理variant的NULL情况
  AND (
    ($3 IS NULL AND am.variant IS NULL) 
    OR (lower(am.variant) = lower($3))
  )
  -- 处理subname的NULL情况
  AND (
    ($4 IS NULL AND am.subname IS NULL) 
    OR (lower(am.subname) = lower($4))
  );

更简洁的写法（PostgreSQL 9.3+支持） #

PostgreSQL提供了IS NOT DISTINCT FROM操作符，它会自动处理NULL的比较（NULL和NULL比较返回TRUE），结合lower()使用可以大幅简化代码：

SELECT count(*) 
FROM app.car_models am, app.business_entities be 
WHERE am.manufacturer=be.id 
  AND be.public_id=$1 
  AND lower(am.name) IS NOT DISTINCT FROM lower($2)
  AND lower(am.variant) IS NOT DISTINCT FROM lower($3)
  AND lower(am.subname) IS NOT DISTINCT FROM lower($4);

验证你的最小复现示例 #

用IS NOT DISTINCT FROM替换=后，你的测试查询就能正确返回结果了：

SELECT count(*) FROM car_models 
WHERE lower(name) IS NOT DISTINCT FROM lower('Name 4') 
  AND lower(variant) IS NOT DISTINCT FROM lower('Variant 4') 
  AND lower(subname) IS NOT DISTINCT FROM lower(null);

这条语句会返回1，成功匹配到id=4的行。

补充说明 #

• lower(null)本身是没问题的：lower()函数接收NULL参数时会返回NULL，这部分符合预期。
• 如果不需要大小写敏感匹配，只需要去掉lower()函数即可，核心还是要处理NULL的比较逻辑。

内容的提问来源于stack exchange，提问作者Pierre H.