Re: array_agg() NULL Handling

"David E. Wheeler" <david(at)kineticode(dot)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