Konstantin Knizhnik <knizhnik@garret.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
