From: Tom Lane <tgl@sss.pgh.pa.us>
To: Konstantin Knizhnik <knizhnik@garret.ru>
cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Contradictory behavior of array_agg(distinct) aggregate.
In-reply-to: <7d3268df-abc2-4eb3-8e2b-59df084b579a@garret.ru>
References: <7d3268df-abc2-4eb3-8e2b-59df084b579a@garret.ru>
Comments: In-reply-to Konstantin Knizhnik <knizhnik@garret.ru>
	message dated "Wed, 04 Dec 2024 08:44:07 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2282299.1733295797.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Wed, 04 Dec 2024 02:03:17 -0500
Message-ID: <2282300.1733295797@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2282300.1733295797%40sss.pgh.pa.us>
Precedence: bulk

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.  "count(distinct x)" does the same
thing --- but count() only counts non-null inputs, so
you end with zero.

			regards, tom lane