Postgresql | remove all arrays that contains by others-CodePudding

Let's for example I have the next table:

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

And insert rows it:

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

So, I have now in the table:

I want to have a query that return only arrays which are unique (in a manner that are not contains by other arrays) So, the return will be rows number 2 & 4 (the arr column)

CodePudding user response：

This can be don using a NOT EXISTS condition:

select t1.*
from temp t1
where not exists (select * 
                  from temp t2
                  where t1.id <> t2.id
                  and t2.arr @> t1.arr);