Re: Need help with SQL query and finding NULL array_agg - Mailing list pgsql-general

On 2012-08-01 23:59, David Johnston wrote:
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of Condor
>> Sent: Wednesday, August 01, 2012 4:16 PM
>> To: pgsql-general@postgresql.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.


Hello,
I understand what is the problem, but I can't combine your example with
my case.

I write my example in middle of the night and may be I miss to say
explain much more
about the structure:


     mob         service    phone     month
      132999      64                     1
  1321543434      66       13255555      1
  1321543434      67       13255555      2

First record when phone is empty and mob is 132999 the number is
correct.
Second two records also is correct, but the phone is not empty so I
need
that filed phone, they have services and month when to start.
I'm unable to use phone_agg[1] IS NULL  because sql return me error
that can't
use the phone_agg[1]

Im expect that result:
    month | status  | service |         array_agg
---------+---------+---------+---------------------------
    {07}  |  {0}    |  {64}   | {132999}
  {08,07} | {0,0}   | {66,67} | {13255555,13255555}


In the end I will have arrays for every phone which service will use.