public inbox for [email protected]  

help / color / mirror / Atom feed

From: Konstantin Knizhnik <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Contradictory behavior of array_agg(distinct) aggregate.
Date: Wed, 4 Dec 2024 08:44:07 +0200
Message-ID: <[email protected]> (raw)

Hi hackers!


Is it only me who consider that current behavior of array_agg(distinct) 
contradicts to interpretation of nulls in other cases ("null" is 
something like "unknown" which means that we can not say weather two 
nulls are the same or not). This is why it is allowed to insert multiple 
nulls in the unique column.

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)


So what is the number of distinct "x" values in this case? I think that 
according to SQL model - 0 (as count(distinct) returns).
Why in this case array_agg(distinct x) returns non-empty array?

Yes, unlike most other aggregates, `array_agg` is not ignoring null values.
But is it correct to treat two null values as the same (non-distinct)?
IMHO correct result in this case should be either {} or NULL, either 
{NULL,NULL}.

view thread (3+ messages)  latest in thread

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