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

Re: array_agg() NULL Handling

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Thom Brown <thom(at)linux(dot)com>
Cc: David Fetter <david(at)fetter(dot)org>, "David E(dot) Wheeler" <david(at)kineticode(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: array_agg() NULL Handling
Date: 2010-09-01 18:09:48
Message-ID: AANLkTik4Q8_Ddjwk8BLRUu51mRHShG5JG4QB5vP7Pbt-@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
2010/9/1 Thom Brown <thom(at)linux(dot)com>:
> On 1 September 2010 18:47, David Fetter <david(at)fetter(dot)org> wrote:
>> On Wed, Sep 01, 2010 at 08:16:41AM -0700, David Wheeler wrote:
>>> On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote:
>>>
>>> > Docs is wrong :) I like current implementation.  You can remove a
>>> > NULLs from aggregation very simply, but different direction isn't
>>> > possible
>>>
>>> Would 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.

Then you can eliminate NULLs with simple function

CREATE OR REPLACE FUNCTION remove_null(anyarray)
RETURNS anyarray AS $$
SELECT ARRAY(SELECT x FROM unnest($1) g(x) WHERE x IS NOT NULL)
$$ LANGUAGE sql;
> --
> Thom Brown
> Twitter: @darkixion
> IRC (freenode): dark_ixion
> Registered Linux user: #516935
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

In response to

Responses

pgsql-hackers by date

Next:From: David E. WheelerDate: 2010-09-01 18:12:37
Subject: Re: array_agg() NULL Handling
Previous:From: David E. WheelerDate: 2010-09-01 18:06:51
Subject: Re: array_agg() NULL Handling

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