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

• Re: array_agg() NULL Handling at 2010-09-01 17:52:35 from Thom Brown

Responses

• Re: array_agg() NULL Handling at 2010-09-02 09:47:14 from Dimitri Fontaine

pgsql-hackers by date

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