Re: Contradictory behavior of array_agg(distinct) aggregate.

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.