PostgreSQL数组‘NOT包含’筛选不符合预期问题求助

你遇到的问题大概率是blacklist字段存在NULL值导致的。

PostgreSQL里，当字段值为NULL时，NULL @> '{"example@gmail.com"}'的计算结果是NULL，而NOT NULL依然是NULL。WHERE子句只会保留条件计算结果为TRUE的行，那些blacklist为NULL的行就会被过滤掉。如果之前单独查询得到的结果里，所有行的blacklist都是NULL，那加上这个条件后自然返回0条结果。

解决方法有两种：

• 用COALESCE把NULL转换成空数组，统一处理：

SELECT *
FROM "Organization"
WHERE "allowedDomains" @> '{"gmail.com"}'
AND NOT COALESCE("blacklist", '{}'::text[]) @> '{"example@gmail.com"}';

• 明确判断NULL的情况，再结合不包含的条件：

SELECT *
FROM "Organization"
WHERE "allowedDomains" @> '{"gmail.com"}'
AND ("blacklist" IS NULL OR NOT "blacklist" @> '{"example@gmail.com"}');

你可以试试这两种写法，应该就能得到预期的结果了。

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