Re: array_agg() NULL Handling

On Sep 1, 2010, at 10:52 AM, Thom Brown wrote:

>>> ould appreciate the recipe for removing the NULLs.
>>
>> WHERE clause :P
>
> There may be cases where that's undesirable, such as there being more
> than one aggregate in the SELECT list, or the column being grouped on
> needing to return rows regardless as to whether there's NULLs in the
> column being targeted by array_agg() or not.

Exactly the issue I ran into:
   SELECT name AS distribution,          array_agg(              CASE relstatus WHEN 'stable'              THEN version
            ELSE NULL          END ORDER BY version) AS stable,          array_agg(              CASE relstatus
    WHEN 'testing'              THEN version              ELSE NULL          END ORDER BY version) AS testing     FROM
distributions   GROUP BY name; 
     distribution │      stable       │      testing           ──────────────┼───────────────────┼────────────────────
  pair         │ {NULL,1.0.0,NULL} │ {0.0.1,NULL,1.2.0}     pgtap        │ {NULL}            │ {0.0.1}    (2 rows) 

Annoying.

Best,

David