PostgreSQL中column=ANY(array)含NULL无返回结果的问题

核心逻辑拆解 #

首先得明确PostgreSQL里两个关键规则：

• NULL比较特性：任何值和NULL做=比较，结果都是NULL，既不是true也不是false。比如NULL = NULL、1 = NULL的结果全是NULL。
• ANY运算符判定规则：expression = ANY(array)会逐一用expression和数组元素比较，只要有一个比较结果为true，ANY就返回true；如果所有比较结果都是NULL/false，ANY返回NULL。

你的查询为什么没返回结果？ #

按你描述，表中有column1=1和column1=NULL的记录，正常来说：

• 对于column1=1的行：和数组里的1比较得到true，ANY直接返回true，这部分行应该被查询出来。
• 对于column1=NULL的行：和数组里的1、NULL比较结果都是NULL，ANY返回NULL，WHERE子句会过滤掉结果为NULL的行，所以这部分行不会被返回。

如果你的查询连column1=1的行都没返回，大概率是数组参数的类型和column1的类型不匹配：
比如column1是integer类型，但你传递的@some_array是text[]类型（比如用字符串"[1, NULL]"代替数组），此时整数和字符串的比较结果全是NULL，ANY返回NULL，所有行都被过滤。

解决办法 #

1. 保证参数类型匹配：
   传递数组时要和列类型一致，比如column1是整数，就传integer[]类型的参数，写法为ARRAY[1, NULL]。
2. 如果要同时匹配NULL值：
   要单独处理NULL的匹配逻辑，修改查询语句：

   SELECT * FROM table1 
   WHERE column1 = ANY(@some_array) 
      OR (column1 IS NULL AND NULL = ANY(@some_array));
   

   或者更简洁的写法（替换integer[]为你的列类型）：

   SELECT * FROM table1 
   WHERE column1 = ANY(@some_array) 
      OR (column1 IS NULL AND @some_array @> ARRAY[NULL]::integer[]);
   

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