Select values that exist in all arrays in Postgres

Question

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)

What exactly is the result you want? What should be the result if you query for the number 5? — user330315
– user330315, Commented Aug 29, 2013 at 11:01
Result is array of enrty_id (from ignored_entry_ids column) that ignored by all users. In other words^ numbers that exists in all rows in ignored_entry_ids column. — Andrey Sbrodov
– Andrey Sbrodov, Commented Aug 29, 2013 at 11:20

roman · Accepted Answer · 2013-08-29 11:05:47Z

3

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)

answered Aug 29, 2013 at 11:05

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select values that exist in all arrays in Postgres

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