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