Skip site navigation (1) Skip section navigation (2)

Re: array_agg() NULL Handling

From: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
To: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
Cc: Thom Brown <thom(at)linux(dot)com>, David Fetter <david(at)fetter(dot)org>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: array_agg() NULL Handling
Date: 2010-09-02 09:47:14
Message-ID: 87occgxysd.fsf@hi-media-techno.com (view raw or flat)
Thread:
Lists: pgsql-hackers
"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

In response to

pgsql-hackers by date

Next:From: Itagaki TakahiroDate: 2010-09-02 10:03:56
Subject: Re: register/unregister standby Re: Synchronous replication
Previous:From: Dimitri FontaineDate: 2010-09-02 09:41:51
Subject: Re: register/unregister standby Re: Synchronous replication

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group