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

Re: string_agg delimiter having no effect with order by

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Thom Brown <thom(at)linux(dot)com>, pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: string_agg delimiter having no effect with order by
Date: 2010-08-04 16:40:09
Message-ID: AANLkTik+sKjNk-ocX86MXDK-U9xVmTdMtrC3aiTGxxkh@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
On Wed, Aug 4, 2010 at 11:29 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Oh, yeah.  I guess you need this:
>
>> select thing, string_agg(stuff, ',' order by stuff) from agg_test
>> group by thing;
>
>> Rather than this:
>
>> select thing, string_agg(stuff order by stuff, ',') from agg_test
>> group by thing;
>
>> It's all kinds of not obvious to me what the second one is supposed to
>> mean, but I remember this was discussed before.  Perhaps we need a
>> <note> somewhere about multi-argument aggregates.
>
> Done:
>
> +    <para>
> +     When dealing with multiple-argument aggregate functions, note that the
> +     <literal>ORDER BY</> clause goes after all the aggregate arguments.
> +     For example, this:
> + <programlisting>
> + SELECT string_agg(a, ',' ORDER BY a) FROM table;
> + </programlisting>
> +     not this:
> + <programlisting>
> + SELECT string_agg(a ORDER BY a, ',') FROM table;  -- not what you want
> + </programlisting>
> +     The latter syntax will be accepted, but <literal>','</> will be
> +     treated as a (useless) sort key.
> +    </para>

Oh, right, that's what it's supposed to mean.  Thanks for adding this.
 I suppose this confusion is only possible because string_agg has both
a one-argument and a two-argument form.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-08-04 16:44:40
Subject: Re: string_agg delimiter having no effect with order by
Previous:From: Joshua TolleyDate: 2010-08-04 16:17:35
Subject: Re: GROUPING SETS revisited

pgsql-bugs by date

Next:From: Robert HaasDate: 2010-08-04 16:42:04
Subject: Re: BUG #5598: Compatibility modes
Previous:From: Tom LaneDate: 2010-08-04 16:37:23
Subject: Re: In 8.2, shutdown wrongly caused automatic restart

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