On 4 August 2010 10:44, Thom Brown <t...@linux.com> wrote: > 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 >
Actually, this rings a bell. I think this may have been raised before, something to do with the delimiter being accepted as one of the order by values. If this isn't really a bug, could someone mention it in the docs somewhere? Thom -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
