Re: [HACKERS] array_agg() NULL Handling
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
David E. Wheeler da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
On 1 September 2010 06:45, David E. Wheeler da...@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.
--
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
Re: [HACKERS] array_agg() NULL Handling
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
2010/9/1 David E. Wheeler da...@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) 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?
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
On 1 September 2010 07:56, Thom Brown t...@linux.com wrote:
On 1 September 2010 06:45, David E. Wheeler da...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
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 the given expression(s) yield non-null values. (Actually, it is up to the aggregate function ! whether to ignore null values or not mdash; but all the standard ones do.) The second form is the same as the first, since literalALL/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 mdash; but all the standard ! ones except functionarray_agg/ do.) The second form is the same as the first, since literalALL/literal is the default. The third form invokes the aggregate for all distinct values of the expressions found 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
Re: [HACKERS] array_agg() NULL Handling
David E. Wheeler da...@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 mdash; but all the standard ones do.) The second form is the same as the first, since literalALL/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 mdash; but all the standard ! ones except functionarray_agg/ do.) The second form is the same as the first, since literalALL/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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
David E. Wheeler da...@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 same as the first, since literalALL/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 functioncount(*)/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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
On 1 September 2010 18:47, David Fetter da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
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 literalALL/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 functioncount(*)/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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
2010/9/1 Thom Brown t...@linux.com: On 1 September 2010 18:47, David Fetter da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
David E. Wheeler da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
David E. Wheeler da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
2010/9/1 Tom Lane t...@sss.pgh.pa.us: David E. Wheeler da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
Pavel Stehule pavel.steh...@gmail.com writes: 2010/9/1 Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] array_agg() NULL Handling
2010/9/1 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2010/9/1 Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
