PostgreSQL array column, querying and grouping by array field

postgresqlpostgresql-9.4

I have the following table data in a table named queryables in Postgres 9.4:

 queryable_id | liked_count | users_who_like_ids 
--------------+-------------+--------------------
         2376 |           3 | {1,80,78,101, 188}
        18771 |           1 | {78,101, 123,125}
         1790 |           1 | {78}
         2257 |           1 | {78}

and I want to select for users who like 1,78,80

The user_who_like_ids column was defined as a text array:

 users_who_like_ids      | text[]                      | default '{}'::text[]

but I has been updated to an integer.

users_who_like_ids      | integer[]                   | default '{}'::integer[]

I perform my query as this:

SELECT * FROM  queryables WHERE users_who_like_ids  @> ARRAY['1','78','80'];

but this requires users_who_like_ids to match ALL of them. I'd like it to be ANY of them, and then group by the length.

How would I do this?

Best Answer

Use the "overlaps" operator:

select * 
from  queryables 
where array[users_who_like_ids]  && ARRAY['1','78','80'];

Your design is really quesionable: first why are you storing numbers as strings? And secondly why don't you store this as a properly normalized one-to-many relationship?

Related Question