I'd like to report a potential bug (or just my misunderstanding), but
I couldn't find any mention in the TODO or on the mailing list.

I'm using PostgreSQL 9.0 beta 3 on Gentoo x64 (sorry, I don't have
beta 4 yet).  I attempted to use string_agg to get values into a
comma-separated list as follows.

test=# create table agg_test (
id serial,
thing integer,
stuff text);
NOTICE:  CREATE TABLE will create implicit sequence "agg_test_id_seq"
for serial column "agg_test.id"
CREATE TABLE

test=# insert into agg_test (thing, stuff) values (1,'meow'),(1,'bark');
INSERT 0 2

test=# select thing, string_agg(stuff order by stuff, ',') from
agg_test group by thing;
 thing | string_agg
-------+------------
     1 | barkmeow
(1 row)

test=# select thing, string_agg(stuff order by thing, ',') from
agg_test group by thing;
 thing | string_agg
-------+------------
     1 | meowbark
(1 row)

As you can see, the output of string_agg isn't delimited.  But if I
remove order by, it works:

test=# select thing, string_agg(stuff, ',') from agg_test group by thing;
 thing | string_agg
-------+------------
     1 | meow,bark
(1 row)

The reason I expect this to work is because of what is stated in the
documentation: 
http://www.postgresql.org/docs/9.0/static/functions-aggregate.html

"This ordering is unspecified by default, but can be controlled by
writing an ORDER BY clause within the aggregate call, as shown in
Section 4.2.7. "

Thanks

-- 
Thom Brown
Registered Linux user: #516935

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply via email to