On 4 August 2010 10:36, Thom Brown <t...@linux.com> wrote: > 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 >
I also notice that there are no regression tests for use of string_agg with both ORDER BY and a delimiter. Thom -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs