PostgreSQL中列与空数组匹配返回空集的原因及解决办法

为什么id != any('{}'::int[])返回空结果？ #

在PostgreSQL中，ANY操作符作用于空数组时，整个表达式的计算结果为NULL。SQL的WHERE子句只会保留条件结果为TRUE的行，而id != NULL的结果是NULL，因此所有行都被过滤，最终返回空结果集。

实现空数组时返回所有记录的方案 #

方案1：判断数组是否为空，结合逻辑或 #

通过array_length函数判断数组为空，此时直接返回所有行；数组非空时执行原有的过滤逻辑：

SELECT * FROM sb.test
WHERE array_length('{}'::int[], 1) IS NULL
   OR id != ANY('{}'::int[]);

方案2：使用ALL操作符 #

PostgreSQL中，ALL操作符作用于空数组时会返回TRUE（空真逻辑：空集合中所有元素都满足条件），因此可以直接替换ANY：

SELECT * FROM sb.test
WHERE id != ALL('{}'::int[]);

这个写法更简洁，当数组为空时条件成立，返回所有行；当数组非空时，会排除所有与数组中元素相等的行。

方案3：明确处理空数组的过滤逻辑 #

如果需求是排除数组中的元素，空数组时返回所有行，也可以用以下写法：

SELECT * FROM sb.test
WHERE NOT (id = ANY('{}'::int[]))
   OR array_length('{}'::int[], 1) IS NULL;

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