Ken Tanzer wrote > > SELECT client_id, > COALESCE( > (SELECT array_agg(code) FROM ( > SELECT distinct > client_id,unnest(accessed_health_care_non_urgent_codes) AS code > FROM service_reach > WHERE client_id=client.client_id > AND service_date BETWEEN '2013-08-01' AND '2013-08-31' > ) foo > ),array['(none)']) > AS accessed_health_care_non_urgent_codes > FROM client; Equivalent semantics: WITH clients_with_codes AS ( SELECT client_id, array_agg(code) AS client_codes FROM (SELECT client_id, unnest(accessed...) AS code FROM service_reach) foo GROUP BY client_id ) SELECT client_id, COALESCE(client_codes, ARRAY['(none)']) AS client_codes FROM client LEFT JOIN client_with_codes USING (client_id) Should (recommend testing) perform better due to the simple fact that you avoid the correlated sub-query (i.e., a sub-query that references the outer query to obtain some parameter - in this case the client_id of the current row). The goal is to create an uncorrelated sub-query/relation that contains all the data you require then JOIN it with the original outer relation using the same equality you were using in the correlated version. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-does-this-array-query-fail-tp5771165p5771366.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
