Thread: array_agg() NULL Handling
The aggregate docs say: > The first form of aggregate expression invokes the aggregate across all input rows for which the given expression(s) yieldnon-null values. (Actually, it is up to the aggregate function whether to ignore null values or not — but all the standardones do.) -- http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES That, however, is not true of array_agg(): try=# CREATE TABLE foo(id int); CREATE TABLE try=# INSERT INTO foo values(1), (2), (NULL), (3); INSERT 0 4 try=# select array_agg(id) from foo; array_agg ──────────────{1,2,NULL,3} (1 row) So are the docs right, or is array_agg() right? Best, David
On 1 September 2010 06:45, David E. Wheeler <david@kineticode.com> wrote: > The aggregate docs say: > >> The first form of aggregate expression invokes the aggregate across all input rows for which the given expression(s) yieldnon-null values. (Actually, it is up to the aggregate function whether to ignore null values or not — but all the standardones do.) > > -- http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES > > That, however, is not true of array_agg(): > > try=# CREATE TABLE foo(id int); > CREATE TABLE > try=# INSERT INTO foo values(1), (2), (NULL), (3); > INSERT 0 4 > try=# select array_agg(id) from foo; > array_agg > ────────────── > {1,2,NULL,3} > (1 row) > > So are the docs right, or is array_agg() right? I think it might be both. array_agg doesn't return NULL, it returns an array which contains NULL. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On Aug 31, 2010, at 11:56 PM, Thom Brown wrote: >>> The first form of aggregate expression invokes the aggregate across all input rows for which the given expression(s)yield non-null values. (Actually, it is up to the aggregate function whether to ignore null values or not —but all the standard ones do.) >> >> -- http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES >> >> That, however, is not true of array_agg(): >> >> try=# CREATE TABLE foo(id int); >> CREATE TABLE >> try=# INSERT INTO foo values(1), (2), (NULL), (3); >> INSERT 0 4 >> try=# select array_agg(id) from foo; >> array_agg >> ────────────── >> {1,2,NULL,3} >> (1 row) >> >> So are the docs right, or is array_agg() right? > > I think it might be both. array_agg doesn't return NULL, it returns > an array which contains NULL. No, string_agg() doesn't work this way, for example: select string_agg(id::text, ',') from foo;string_agg ────────────1,2,3 (1 row) Note that it's not: select string_agg(id::text, ',') from foo;string_agg ────────────1,2,,3 (1 row) Best, David
2010/9/1 David E. Wheeler <david@kineticode.com>: > The aggregate docs say: > >> The first form of aggregate expression invokes the aggregate across all input rows for which the given expression(s) yieldnon-null values. (Actually, it is up to the aggregate function whether to ignore null values or not — but all the standardones do.) > > -- http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES > > That, however, is not true of array_agg(): > > try=# CREATE TABLE foo(id int); > CREATE TABLE > try=# INSERT INTO foo values(1), (2), (NULL), (3); > INSERT 0 4 > try=# select array_agg(id) from foo; > array_agg > ────────────── > {1,2,NULL,3} > (1 row) > > So are the docs right, or is array_agg() right? Docs is wrong :) I like current implementation. You can remove a NULLs from aggregation very simply, but different direction isn't possible Regards Pavel Stehule > > Best, > > David > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On 1 September 2010 07:56, Thom Brown <thom@linux.com> wrote: > On 1 September 2010 06:45, David E. Wheeler <david@kineticode.com> wrote: >> The aggregate docs say: >> >>> The first form of aggregate expression invokes the aggregate across all input rows for which the given expression(s)yield non-null values. (Actually, it is up to the aggregate function whether to ignore null values or not —but all the standard ones do.) >> >> -- http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES >> >> That, however, is not true of array_agg(): >> >> try=# CREATE TABLE foo(id int); >> CREATE TABLE >> try=# INSERT INTO foo values(1), (2), (NULL), (3); >> INSERT 0 4 >> try=# select array_agg(id) from foo; >> array_agg >> ────────────── >> {1,2,NULL,3} >> (1 row) >> >> So are the docs right, or is array_agg() right? > > I think it might be both. array_agg doesn't return NULL, it returns > an array which contains NULL. The second I wrote that, I realised it was b*ll%$ks, as I was still in the process of waking up. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote: > Docs is wrong :) I like current implementation. You can remove a NULLs > from aggregation very simply, but different direction isn't possible Would appreciate the recipe for removing the NULLs. Best, David
On Sep 1, 2010, at 1:06 AM, Thom Brown wrote: >> I think it might be both. array_agg doesn't return NULL, it returns >> an array which contains NULL. > > The second I wrote that, I realised it was b*ll%$ks, as I was still in > the process of waking up. I know that feeling. /me sips his coffee Best, David
On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote: >> So are the docs right, or is array_agg() right? > > Docs is wrong :) I like current implementation. You can remove a NULLs > from aggregation very simply, but different direction isn't possible Patch: diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 9f91939..e301019 100644 *** a/doc/src/sgml/syntax.sgml --- b/doc/src/sgml/syntax.sgml *************** sqrt(2) *** 1543,1549 **** The first form of aggregate expression invokes the aggregate across all input rows for which thegiven expression(s) yield non-null values. (Actually, it is up to the aggregate function ! whether to ignore null values or not — but all the standard ones do.) The second form is the same as thefirst, since <literal>ALL</literal> is the default. The third form invokes the aggregate for all distinct valuesof the expressions found --- 1543,1550 ---- The first form of aggregate expression invokes the aggregate across all input rows for which thegiven expression(s) yield non-null values. (Actually, it is up to the aggregate function ! whether to ignore null values or not — but all the standard ! ones except <function>array_agg</> do.) The second form is the same as the first, since <literal>ALL</literal>is the default. The third form invokes the aggregate for all distinct values of the expressionsfound Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> *** 1543,1549 ****
> The first form of aggregate expression invokes the aggregate
> across all input rows for which the given expression(s) yield
> non-null values. (Actually, it is up to the aggregate function
> ! whether to ignore null values or not — but all the standard ones do.)
> The second form is the same as the first, since
> <literal>ALL</literal> is the default. The third form invokes the
> aggregate for all distinct values of the expressions found
> --- 1543,1550 ----
> The first form of aggregate expression invokes the aggregate
> across all input rows for which the given expression(s) yield
> non-null values. (Actually, it is up to the aggregate function
> ! whether to ignore null values or not — but all the standard
> ! ones except <function>array_agg</> do.)
> The second form is the same as the first, since
> <literal>ALL</literal> is the default. The third form invokes the
> aggregate for all distinct values of the expressions found
I think when that text was written, it was meant to imply "all the
aggregates defined in SQL92". There seems to be a lot of confusion
in this thread about whether "standard" means "defined by SQL spec"
or "built-in in Postgres". Should we try to refine the wording to
clarify that?
Even more to the point, should we deliberately make this vaguer so that
we aren't finding ourselves with obsolete text again and again? You can
bet that people adding new aggregates in the future aren't going to
think to update this sentence, any more than happened with array_agg.
regards, tom lane
On Sep 1, 2010, at 10:12 AM, Tom Lane wrote: > I think when that text was written, it was meant to imply "all the > aggregates defined in SQL92". There seems to be a lot of confusion > in this thread about whether "standard" means "defined by SQL spec" > or "built-in in Postgres". Should we try to refine the wording to > clarify that? Yes please. > Even more to the point, should we deliberately make this vaguer so that > we aren't finding ourselves with obsolete text again and again? You can > bet that people adding new aggregates in the future aren't going to > think to update this sentence, any more than happened with array_agg. Perhaps “consult the docs for each aggregate to determine how it handles NULLs.” Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> On Sep 1, 2010, at 10:12 AM, Tom Lane wrote:
>> Even more to the point, should we deliberately make this vaguer so that
>> we aren't finding ourselves with obsolete text again and again? You can
>> bet that people adding new aggregates in the future aren't going to
>> think to update this sentence, any more than happened with array_agg.
> Perhaps �consult the docs for each aggregate to determine how it handles NULLs.�
Hm, actually the whole para needs work. It was designed at a time when
DISTINCT automatically discarded nulls, which isn't true anymore, and
that fact was patched-in in a very awkward way too. Perhaps something
like
The first form of aggregate expression invokes the aggregate once for each input row. The second form is the
sameas the first, since <literal>ALL</literal> is the default. The third form invokes the aggregate once for each
distinctvalue, or set of values, of the expression(s) found in the input rows. The last form invokes the aggregate
oncefor each input row; since no particular input value is specified, it is generally only useful for the
<function>count(*)</function>aggregate function.
Most aggregate functions ignore null inputs, so that rows in which one or more of the expression(s) yield null are
discarded. (This can be assumed to be true, unless otherwise specified, for all built-in aggregates.)
Then we have to make sure array_agg is properly documented, but we
don't have to insert something into the description of every single
aggregate, which is what your proposal would require.
regards, tom lane
On Wed, Sep 01, 2010 at 08:16:41AM -0700, David Wheeler wrote: > On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote: > > > Docs is wrong :) I like current implementation. You can remove a > > NULLs from aggregation very simply, but different direction isn't > > possible > > Would appreciate the recipe for removing the NULLs. WHERE clause :P Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On 1 September 2010 18:47, David Fetter <david@fetter.org> wrote: > On Wed, Sep 01, 2010 at 08:16:41AM -0700, David Wheeler wrote: >> On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote: >> >> > Docs is wrong :) I like current implementation. You can remove a >> > NULLs from aggregation very simply, but different direction isn't >> > possible >> >> Would appreciate the recipe for removing the NULLs. > > WHERE clause :P There may be cases where that's undesirable, such as there being more than one aggregate in the SELECT list, or the column being grouped on needing to return rows regardless as to whether there's NULLs in the column being targeted by array_agg() or not. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On Sep 1, 2010, at 10:52 AM, Thom Brown wrote:
>>> ould appreciate the recipe for removing the NULLs.
>>
>> WHERE clause :P
>
> There may be cases where that's undesirable, such as there being more
> than one aggregate in the SELECT list, or the column being grouped on
> needing to return rows regardless as to whether there's NULLs in the
> column being targeted by array_agg() or not.
Exactly the issue I ran into:
SELECT name AS distribution, array_agg( CASE relstatus WHEN 'stable' THEN version
ELSE NULL END ORDER BY version) AS stable, array_agg( CASE relstatus
WHEN 'testing' THEN version ELSE NULL END ORDER BY version) AS testing FROM
distributions GROUP BY name;
distribution │ stable │ testing ──────────────┼───────────────────┼────────────────────
pair │ {NULL,1.0.0,NULL} │ {0.0.1,NULL,1.2.0} pgtap │ {NULL} │ {0.0.1} (2 rows)
Annoying.
Best,
David
On Sep 1, 2010, at 10:30 AM, Tom Lane wrote: > Hm, actually the whole para needs work. It was designed at a time when > DISTINCT automatically discarded nulls, which isn't true anymore, and > that fact was patched-in in a very awkward way too. Perhaps something > like > > The first form of aggregate expression invokes the aggregate > once for each input row. > The second form is the same as the first, since > <literal>ALL</literal> is the default. > The third form invokes the aggregate once for each distinct value, > or set of values, of the expression(s) found in the input rows. > The last form invokes the aggregate once for each input row; since no > particular input value is specified, it is generally only useful > for the <function>count(*)</function> aggregate function. > > Most aggregate functions ignore null inputs, so that rows in which > one or more of the expression(s) yield null are discarded. (This > can be assumed to be true, unless otherwise specified, for all > built-in aggregates.) I don't think you need the parentheses, though without them, "This" might be better written as "The ignoring of NULLs". Just my $0.02. Best, David
2010/9/1 Thom Brown <thom@linux.com>: > On 1 September 2010 18:47, David Fetter <david@fetter.org> wrote: >> On Wed, Sep 01, 2010 at 08:16:41AM -0700, David Wheeler wrote: >>> On Sep 1, 2010, at 12:30 AM, Pavel Stehule wrote: >>> >>> > Docs is wrong :) I like current implementation. You can remove a >>> > NULLs from aggregation very simply, but different direction isn't >>> > possible >>> >>> Would appreciate the recipe for removing the NULLs. >> >> WHERE clause :P > > There may be cases where that's undesirable, such as there being more > than one aggregate in the SELECT list, or the column being grouped on > needing to return rows regardless as to whether there's NULLs in the > column being targeted by array_agg() or not. Then you can eliminate NULLs with simple function CREATE OR REPLACE FUNCTION remove_null(anyarray) RETURNS anyarray AS $$ SELECT ARRAY(SELECT x FROM unnest($1) g(x) WHERE x IS NOT NULL) $$ LANGUAGE sql; > -- > Thom Brown > Twitter: @darkixion > IRC (freenode): dark_ixion > Registered Linux user: #516935 > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Sep 1, 2010, at 11:09 AM, Pavel Stehule wrote: > Then you can eliminate NULLs with simple function > > CREATE OR REPLACE FUNCTION remove_null(anyarray) > RETURNS anyarray AS $$ > SELECT ARRAY(SELECT x FROM unnest($1) g(x) WHERE x IS NOT NULL) > $$ LANGUAGE sql; Kind of defeats the purpose of the efficiency of the aggregate. Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> On Sep 1, 2010, at 10:30 AM, Tom Lane wrote:
>> Most aggregate functions ignore null inputs, so that rows in which
>> one or more of the expression(s) yield null are discarded. (This
>> can be assumed to be true, unless otherwise specified, for all
>> built-in aggregates.)
> I don't think you need the parentheses, though without them, "This" might be better written as "The ignoring of
NULLs".
Done, without the parentheses. I didn't add "The ignoring of NULLs",
it seemed a bit too verbose.
regards, tom lane
"David E. Wheeler" <david@kineticode.com> writes:
> On Sep 1, 2010, at 11:09 AM, Pavel Stehule wrote:
>> Then you can eliminate NULLs with simple function
> Kind of defeats the purpose of the efficiency of the aggregate.
Well, you can build your own version of array_agg with the same
implementation, except you mark the transition function as strict ...
regards, tom lane
2010/9/1 Tom Lane <tgl@sss.pgh.pa.us>: > "David E. Wheeler" <david@kineticode.com> writes: >> On Sep 1, 2010, at 11:09 AM, Pavel Stehule wrote: >>> Then you can eliminate NULLs with simple function > >> Kind of defeats the purpose of the efficiency of the aggregate. > > Well, you can build your own version of array_agg with the same > implementation, except you mark the transition function as strict ... > I am checking this now, and it is not possible - it needs a some initial value and there isn't possible to set a "internal" value. probably some C coding is necessary. Regards Pavel > regards, tom lane >
Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2010/9/1 Tom Lane <tgl@sss.pgh.pa.us>:
>> Well, you can build your own version of array_agg with the same
>> implementation, except you mark the transition function as strict ...
> I am checking this now, and it is not possible - it needs a some
> initial value and there isn't possible to set a "internal" value.
Well, you can cheat a bit ...
regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as
'array_agg_transfn'language internal immutable;
CREATE FUNCTION
regression=# create aggregate array_agg_strict(anyelement) (stype = internal,
sfunc = array_agg_transfn_strict, finalfunc = array_agg_finalfn);
CREATE AGGREGATE
regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as
'array_agg_transfn'language internal strict immutable;
CREATE FUNCTION
regards, tom lane
2010/9/1 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> 2010/9/1 Tom Lane <tgl@sss.pgh.pa.us>: >>> Well, you can build your own version of array_agg with the same >>> implementation, except you mark the transition function as strict ... > >> I am checking this now, and it is not possible - it needs a some >> initial value and there isn't possible to set a "internal" value. > > Well, you can cheat a bit ... > > regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as 'array_agg_transfn'language internal immutable; > CREATE FUNCTION > regression=# create aggregate array_agg_strict(anyelement) (stype = internal, > sfunc = array_agg_transfn_strict, finalfunc = array_agg_finalfn); > CREATE AGGREGATE > regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as 'array_agg_transfn'language internal strict immutable; > CREATE FUNCTION > nice dark trick :) - but it doesn't work ERROR: aggregate 16395 needs to have compatible input type and transition type postgres=# Pavel > regards, tom lane >
"David E. Wheeler" <david@kineticode.com> writes: > On Sep 1, 2010, at 10:52 AM, Thom Brown wrote: > >>>> ould appreciate the recipe for removing the NULLs. >>> >>> WHERE clause :P >> >> There may be cases where that's undesirable, such as there being more >> than one aggregate in the SELECT list, or the column being grouped on >> needing to return rows regardless as to whether there's NULLs in the >> column being targeted by array_agg() or not. > > Exactly the issue I ran into: > > SELECT name AS distribution, > array_agg( > CASE relstatus WHEN 'stable' > THEN version > ELSE NULL > END ORDER BY version) AS stable, > array_agg( > CASE relstatus > WHEN 'testing' > THEN version > ELSE NULL > END ORDER BY version) AS testing > FROM distributions > GROUP BY name; What about adding WHERE support to aggregates, adding to the ORDER BY capability they already have? SELECT array_agg(version WHERE relstatus = 'stable' ORDER BY version) The current way to do that is using a subquery and unnest() and where clause there, but that's not a good way to avoid to process stored data in the aggregate / in the query. Regards, -- dim
On Sep 1, 2010, at 11:52 AM, Pavel Stehule wrote: >> regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as 'array_agg_transfn'language internal immutable; >> CREATE FUNCTION >> regression=# create aggregate array_agg_strict(anyelement) (stype = internal, >> sfunc = array_agg_transfn_strict, finalfunc = array_agg_finalfn); >> CREATE AGGREGATE >> regression=# create or replace function array_agg_transfn_strict(internal, anyelement) returns internal as 'array_agg_transfn'language internal strict immutable; >> CREATE FUNCTION >> > > nice dark trick :) - but it doesn't work > > ERROR: aggregate 16395 needs to have compatible input type and transition type > postgres=# I could use this trick now. Anyone got any bright ideas how to fix it? Thanks, David