Select subarray and its length in PostgreSQL

Question

How can I get subquery array and it`s count?

SELECT location.country,    
       (SELECT array(
            SELECT competition.id
            FROM competition
            WHERE location.id = competition.location_id )) AS list,
       array_length(list, 1) AS list_count
FROM location

I am trying to get competition list for every country. Query works fine without: array_length(list, 1) AS list_count

It is arguing that column 'list' doesn't exists.
Model: http://sqlfiddle.com/#!2/4c449

Answer 1

Probably even simpler / faster: aggregate and count in a separate query and join to it (thereby avoiding your original problem to begin with):

SELECT l.country, c.list, c.list_count
FROM   location l
LEFT   JOIN (
   SELECT location_id AS id
         ,array_agg(id) AS list
         ,count(*) AS list_count
   FROM   competition
   GROUP  BY 1
   ) c USING (id)

Should be faster as long as you fetch large parts of the table or all of it.

Answer 2

select country, 
       list, 
       array_length(list, 1) as list_count
from (
  SELECT country,    
         (SELECT array(
            SELECT competition.id
            FROM competition
            WHERE location.id = competition.location_id )) AS list
  FROM location
) t

Using an aggregate might be faster though:

select country, 
       list, 
       array_length(list, 1) as list_count
from (
  SELECT l.country,    
         array_agg(c.id) as list
  FROM location l
    JOIN competition c ON l.id = c.location_id
  GROUP BY l.country
) t