Postgres filter on array column - removing subarrays

Question

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?

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

user330315 · Accepted Answer · 2022-11-17 11:41:58Z

1

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.

Collectives™ on Stack Overflow

Postgres filter on array column - removing subarrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related