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.

Marcelo Cantos · Accepted Answer · 2010-06-25 13:30:50Z

5

Try this:

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

answered Jun 25, 2010 at 13:30

Marcelo Cantos

187k40 gold badges338 silver badges366 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL count() and arrays

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