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

Can you add an example datamodel on sqlfiddle? http://sqlfiddle.com/#!15 — Wolph, Jan 09 '14 at 10:48
Your question is unclear. You need to add a table definition and define the array. — Erwin Brandstetter, Jan 09 '14 at 11:03
I don't see much of an improvement. Still no table definition. Still your query is incomplete and the question unclear. Also, the description doesn't match the code. `array_length(p,1)` does not even reference `list` ... I can only guess: It's not possible to reference a column alias on the same query level. You would have to use a subquery. — Erwin Brandstetter, Jan 09 '14 at 11:23
@ErwinBrandstetter Thanks for help. You were right about same query level. — Vladimir Nani, Jan 09 '14 at 12:28

score 2 · Accepted Answer · answered Jan 09 '14 at 12:36

2

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.

answered Jan 09 '14 at 12:36

Erwin Brandstetter

605,456
145
1,078
1,228

That makes more sense for me. – Vladimir Nani Jan 09 '14 at 12:55

score 1 · Answer 2 · answered Jan 09 '14 at 12:24

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

Select subarray and its length in PostgreSQL

2 Answers2