PostgreSQL如何查询枚举数组列包含指定数组任意值的记录

存在一张名为projects的数据表，表中operating_system列为枚举数组类型，列数据样例如下：

需要查询operating_system列中包含目标数组['ios', 'windows']任意值的所有项目记录，预期返回第1、4、7、9条记录。
尝试执行的SQL语句如下：

SELECT * FROM public.projects
WHERE operating_system = ANY (['ios', 'windows']);

执行后抛出语法错误：

ERROR:  syntax error at or near "["  
LINE 2: WHERE operating_system = ANY (['ios', 'windows']);

• PostgreSQL不支持直接用[]定义数组字面量，这是触发语法报错的直接原因。
• 即使修正数组写法，= ANY(...)的逻辑是判断字段值是否等于数组列表中的某一个元素，不适用于数组类型字段的交集匹配场景，无法得到预期结果。

PostgreSQL提供了数组重叠运算符&&，专门用来判断两个数组是否存在共同元素，正好匹配“包含目标数组任意值”的需求，推荐写法如下：

SELECT * FROM public.projects
WHERE operating_system && ARRAY['ios', 'windows'];

如果执行时提示类型不匹配，给数组加上对应枚举类型的强转即可，把下方的your_enum_type替换成operating_system列实际对应的枚举类型名：

SELECT * FROM public.projects
WHERE operating_system && ARRAY['ios', 'windows']::your_enum_type[];

也可以使用字符串转数组的写法：

SELECT * FROM public.projects
WHERE operating_system && '{ios, windows}'::your_enum_type[];

• 如果后续需要查询operating_system同时包含目标数组所有值的记录，把运算符替换为@>即可。
• 不建议用ANY搭配数组元素遍历的写法，性能远低于&&运算符。

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