Re: Contradictory behavior of array_agg(distinct) aggregate.

On 04/12/2024 9:03 am, Tom Lane wrote:
> 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.

Sorry.
It is actually inconsistency in basic SQL model in interpretation of
NULL by UNIQUE and DISTINCT, and array_agg just follows this rule.