Re: Contradictory behavior of array_agg(distinct) aggregate.

Konstantin Knizhnik <knizhnik(at)garret(dot)ru> writes:

> 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.

The behaviour of DISTINCT in aggregates matches the behaviour of the IS
(NOT) DISTINCT FROM predicate, which considers NULLs NOT DISTINCT from
eachother.

UNIQUE constraints leave it implementation-defined whether NULLs are
considered distinct (PostgreSQL defaults to NULLS DISTINCT), but that
can be overridden in the constraint definition.

- ilmari