PostgreSQL如何查询表中未被其他数组包含的数组记录

测试环境准备 #

首先创建测试表：

CREATE TABLE temp
(
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    arr bigint[] NOT NULL
);

插入测试数据：

INSERT INTO temp (arr) VALUES
(ARRAY[2, 3]),
(ARRAY[2,3,4]),
(ARRAY[4]),
(ARRAY[1, 2, 3]);

插入完成后表内共4条记录，arr字段对应值分别为[2,3]、[2,3,4]、[4]、[1,2,3]。
需求：查询返回所有未被表中其他任意数组包含的数组记录，预期结果为id=2、id=4两行的arr列数据。

实现SQL #

基于PostgreSQL的数组包含运算符@>（运算符左侧数组包含右侧数组全部元素时返回真），通过NOT EXISTS过滤掉存在被其他记录包含的条目即可，语句如下：

SELECT id, arr
FROM temp t1
WHERE NOT EXISTS (
    SELECT 1
    FROM temp t2
    WHERE t2.id != t1.id
      AND t2.arr @> t1.arr
);

逻辑说明 #

• 逐行判断当前记录t1，如果能找到任意一条id不同的记录t2，且t2的数组包含t1的数组，就排除当前t1记录
• 最终保留的就是不存在任何其他数组能包含它的记录，执行后返回结果和预期一致：id=2的[2,3,4]、id=4的[1,2,3]

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