Re: Why does this array query fail?

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.