Postgres filter on array column - removing subarrays-CodePudding

I have a PostgreSQL table with a column that contains arrays:

| col |
| --- |
| {1,5,6} |
| {5,6,7} |
| {5,6} |
| {5,7} |
| {6,7} |
| {1} |
| {2} |
| {3} |
| {4} |
| {5} |
| {6} |
| {7} |

I want to find all arrays in col which are not wholly contained by another array. The output should look like this:

| col |
| --- |
| {1,5,6} |
| {5,6,7} |
| {2} |
| {3} |
| {4} |

I am hoping there is a PostgreSQL way of doing this. Can anyone help please?

#################################################

CodePudding user response：

You can use a NOT EXISTS condition to find those arrays:

select t1.*
from the_table t1
where not exists (select *
                  from the_table t2
                  where t1.col <@ t2.col
                    and t1.ctid <> t2.ctid)

ctid is an internal unique value for each row and is used to avoid comparing values with itself. If you have a proper primary key column in your table, you should use that instead.

Online example