public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dagfinn Ilmari Mannsåker <[email protected]>
To: Konstantin Knizhnik <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Contradictory behavior of array_agg(distinct) aggregate.
Date: Wed, 04 Dec 2024 10:25:39 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

Konstantin Knizhnik <[email protected]> writes:

> On 04/12/2024 9:03 am, Tom Lane wrote:
>> Konstantin Knizhnik <[email protected]> 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

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Contradictory behavior of array_agg(distinct) aggregate.
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox