Skip site navigation (1) Skip section navigation (2)

Re: string_agg delimiter having no effect with order by

From: Thom Brown <thom(at)linux(dot)com>
To: pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: string_agg delimiter having no effect with order by
Date: 2010-08-04 10:03:52
Message-ID: AANLkTi=nAWr-bWoL9+SrLSR1pAKn4JBiF0kX0iXXCa2S@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
On 4 August 2010 10:44, Thom Brown <thom(at)linux(dot)com> wrote:
> On 4 August 2010 10:36, Thom Brown <thom(at)linux(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-08-04 10:32:11
Subject: Re: documentation for committing with git
Previous:From: Thom BrownDate: 2010-08-04 09:44:32
Subject: Re: string_agg delimiter having no effect with order by

pgsql-bugs by date

Next:From: Robert HaasDate: 2010-08-04 13:04:49
Subject: Re: string_agg delimiter having no effect with order by
Previous:From: Thom BrownDate: 2010-08-04 09:44:32
Subject: Re: string_agg delimiter having no effect with order by

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group