PostgreSQL数组类型字段匹配包含或不包含

我们都知道PostgreSQL数组类型字段查询可以使用 ANY/SOME/ALL 关键字进行筛选。

匹配结果在数组中存在表达式：
WHERE expression operator ANY (subquery)
WHERE expression operator SOME (subquery)
WHERE expression operator ALL(subquery)

官方文档: https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.28.16.

---

此篇我们主要针对结果不存在于数组中的处理：

SELECT * FROM “table” WHERE NOT (some_value = ANY (some_array));
等同于：
SELECT * FROM “table” WHERE (some_value != ANY (some_array));

---

如果"some_array" 字段值可能为null；则必须使用 coalesce() 函数：

SELECT * FROM “table” WHERE NOT (some_value = ANY(coalesce(some_array, array[]::int[])));
等同于：
SELECT * FROM “table” WHERE (some_value != ANY(coalesce(some_array, array[]::int[])));

coalesce()函数说明: https://www.postgresql.org/docs/13/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL.

---