PostgreSQL count() and arrays

Question

I have a table that looks like this:

                           episodes
------------------------------------------------------------
id (PK serial)  |  show_id (int4)  | episode_number (int2[])
------------------------------------------------------------
1               | 1                | {1}
2               | 1                | {2}
3               | 1                | {3}
4               | 1                | {4,5}

Column episode_number is an integer array because there can be special episodes that are a combination of 2. Now I'd like to perform a COUNT() to get the total number of episodes for a certain show.

My query SELECT COUNT(id) FROM episodes WHERE show_id = 1 doesn't work correctly and I have no idea how to get this kind of functionality. It returns 4 and I need a query that takes the total number of array values in count and that should return 5 for the above data.

Any help is appreciated.

Answer 1

Try this:

SELECT SUM(array_length(episode_number, 1))
  FROM episodes
 WHERE show_id = 1