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