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.