PostgreSQL中两个integer数组求交集报运算符不存在错误如何解决

报错原因 #

&数组交集运算符不属于PostgreSQL内核原生功能，是可选扩展intarray提供的扩展运算符，默认未启用，所以执行时会触发operator does not exist: integer[] & integer[]错误。

修复方法 #

使用数据库超级用户执行以下语句启用intarray扩展即可：

CREATE EXTENSION IF NOT EXISTS intarray;

注意：intarray扩展的运算符仅支持integer[]类型，不支持bigint[]、numeric[]等其他数值数组类型
扩展启用后，你原来的查询语句可以正常执行，返回结果为{190,80}。

无扩展替代方案 #

如果不方便安装扩展，可使用原生SQL实现数组交集，兼容所有PostgreSQL版本：

SELECT array_agg(DISTINCT elem) AS array_intersection
FROM unnest('{190,80}'::integer[]) AS t(elem)
WHERE elem = ANY('{190,80,50}'::integer[]);

如果后续升级到PostgreSQL 14及以上版本，可直接使用内置的array_intersect函数实现相同功能，语法更简洁：

SELECT array_intersect('{190,80}'::integer[], '{190,80,50}'::integer[]);

性能说明 #

高频进行整数数组交集计算的场景下，intarray扩展的运算符性能最优，还支持为整数数组创建GIN索引加速关联过滤查询。

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