Re: string_agg delimiter having no effect with order by

On 4 August 2010 10:36, Thom Brown <thom@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). =A0I attempted to use string_agg to get values into a
> comma-separated list as follows.
>
> test=3D# create table agg_test (
> id serial,
> thing integer,
> stuff text);
> NOTICE: =A0CREATE TABLE will create implicit sequence "agg_test_id_seq"
> for serial column "agg_test.id"
> CREATE TABLE
>
> test=3D# insert into agg_test (thing, stuff) values (1,'meow'),(1,'bark');
> INSERT 0 2
>
> test=3D# select thing, string_agg(stuff order by stuff, ',') from
> agg_test group by thing;
> =A0thing | string_agg
> -------+------------
> =A0 =A0 1 | barkmeow
> (1 row)
>
> test=3D# select thing, string_agg(stuff order by thing, ',') from
> agg_test group by thing;
> =A0thing | string_agg
> -------+------------
> =A0 =A0 1 | meowbark
> (1 row)
>
> As you can see, the output of string_agg isn't delimited. =A0But if I
> remove order by, it works:
>
> test=3D# select thing, string_agg(stuff, ',') from agg_test group by thin=
g;
> =A0thing | string_agg
> -------+------------
> =A0 =A0 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-aggreg=
ate.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