On 04/12/2024 9:03 am, Tom Lane wrote:
> 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.
Sorry.
It is actually inconsistency in basic SQL model in interpretation of
NULL by UNIQUE and DISTINCT, and array_agg just follows this rule.
