How to check if all array elements exists in set of data in table

Question

I have array of integer values: [1,2,3,4,6,52]

I would like to check if this set of integer values exists in postgres table sets

Table and sets structure:

In this example my array values exists in second table set.

How can i solve this problem?

Answer 1

If you want one row per door_id whose set of available_id is exactly the same as the given array, you can use aggregation like so:

select door_id
from mytable
group by door_id
having array_agg(available_id order by available_id) = array[1,2,3,4,6,52]

If you are fine with subset containment:

having array_agg(available_id order by available_id) @> array[1,2,3,4,6,52]

If you want all corresponding rows, use window aggregation:

select *
from (
    select t.*, 
        array_agg(available_id order by available_id) over(partition by door_id) arr
    from mytable t
) t
where arr @> array[1,2,3,4,6,52]