Contradictory behavior of array_agg(distinct) aggregate.
От | Konstantin Knizhnik |
---|---|
Тема | Contradictory behavior of array_agg(distinct) aggregate. |
Дата | |
Msg-id | 7d3268df-abc2-4eb3-8e2b-59df084b579a@garret.ru обсуждение исходный текст |
Ответы |
Re: Contradictory behavior of array_agg(distinct) aggregate.
Re: Contradictory behavior of array_agg(distinct) aggregate. |
Список | pgsql-hackers |
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}.
В списке pgsql-hackers по дате отправления: