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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers