Re: array_agg() NULL Handling

"David E. Wheeler" <david@kineticode.com> writes:
> 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;

What about adding WHERE support to aggregates, adding to the ORDER BY
capability they already have?
 SELECT array_agg(version WHERE relstatus = 'stable' ORDER BY version)

The current way to do that is using a subquery and unnest() and where
clause there, but that's not a good way to avoid to process stored data
in the aggregate / in the query.

Regards,
-- 
dim