Re: Need help with SQL query and finding NULL array_agg

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Condor
> Sent: Wednesday, August 01, 2012 4:16 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Need help with SQL query and finding NULL array_agg
>
> Hello ppl,
>
> I have a problem with one sql query, can some one help me. My query is:
>
> SELECT array_agg(month) AS month, array_agg(status) AS status,
> array_agg(service) AS service, case when array_upper(array_agg(phone),
> 1) is not null THEN array_agg(phone) else array_agg(mobile) END FROM bills
> WHERE status > 1 GROUP BY mobile
>
> I try with simple query to identify did array_agg(phone) is empty or null and if
> is it to return me mobile field, if not empty to return me phone. Mobile field
> exist always, but phone may exists for that mobile may not exists.
> One mobile can have few services like:
>
>
> mob service phone
> 1321543434 64
> 1321543434 66 13255555
>
>
> I try few thing but sql only return me records that phone is not empty, but I
> need them both.
>
> Any one has ideas what I can do ?

ARRAY_AGG() is never an empty array since there is always at least a single record that is going to be aggregated. In your case your array will have NULL "values" when phone numbers are missing but the upper bound will still show a positive number.

SELECT array_upper(ARRAY[NULL]::varchar[], 1) == 1

You would need to write a custom aggregation that ignores NULL and thus could return an empty array if no valid phone numbers are present.

The proper logic would be:

CASE WHEN array_upper(phone_agg, 1) = 1 AND phone_agg[1] IS NULL THEN ... END

You also likely want to use:

ARRAY_AGG(DISTINCT column) -- this makes sure duplicates are only present a single time and ensure that an all-NULL situation results in a single element instead of one NULL for each input record.

Hopefully this help because I couldn't make heads nor tails as to what exactly your issue is. The lack of input data, the current output, and the desired output limits my ability to understand and help.

One last comment: I would generally avoid naming the output of an ARRAY_AGG(column) the same name as the input column. I generally, at minimum, make the output column name plural to reflect the fact that it contains multiple values of whatever is stored in the source column.

David J.