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

Re: array_agg() NULL Handling

From: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
To: Thom Brown <thom(at)linux(dot)com>
Cc: 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-01 17:59:42
Message-ID: F9B5FA27-B90B-4D4A-A3A4-DB85BE96D2BE@kineticode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: David E. WheelerDate: 2010-09-01 18:06:51
Subject: Re: array_agg() NULL Handling
Previous:From: Thom BrownDate: 2010-09-01 17:52:35
Subject: Re: array_agg() NULL Handling

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