Re: Contradictory behavior of array_agg(distinct) aggregate.

Konstantin Knizhnik <knizhnik(at)garret(dot)ru> writes:
> postgres=# create table t(x integer unique);
> CREATE TABLE
> postgres=# insert into t values (null),(null);
> INSERT 0 2
> postgres=# select count(distinct x) from t;
>  count
> -------
>      0
> (1 row)

> postgres=# select array_agg(distinct x) from t;
>  array_agg
> -----------
>  {NULL}
> (1 row)

> postgres=# select array_agg(x) from t;
>   array_agg
> -------------
>  {NULL,NULL}
> (1 row)

I see nothing contradictory here. "array_agg(distinct x)"
combines the two NULLs into one, which is the normal
behavior of DISTINCT. "count(distinct x)" does the same
thing --- but count() only counts non-null inputs, so
you end with zero.

regards, tom lane