CODEWITHSUNDEEP
sqlpostgresql
Andrey Sbrodov
Andrey Sbrodov

Reputation: 88

Select values that exist in all arrays in Postgres

I've got some table ignore with col ignored_entry_ids contains array of integer. For example:

id     ignored_entry_ids
1      {1,4,6}
2      {6,8,11}
3      {5,6,7}

How can I select numbers that exists in every row with array? (6 in examle)

Upvotes: 1

Views: 115

Answers (1)

roman
roman

Reputation: 117380

If your numbers are unique inside array, you can do something like this, don't think it could be made without unnest

with cte as (
    select id, unnest(ignored_entry_ids) as arr
    from ign
)
select arr
from cte
group by arr
having count(*) = (select count(*) from ign)

sql fiddle demo

if numbers are not unique, add distinct:

with cte as (
    select distinct id, unnest(ignored_entry_ids) as arr
    from ign
)
select arr
from cte
group by arr
having count(*) = (select count(*) from ign)

Upvotes: 3

Related Questions