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
