From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | pgsql-bugs <pgsql-bugs(at)postgresql(dot)org> |
Subject: | string_agg delimiter having no effect with order by |
Date: | 2010-08-04 09:36:34 |
Message-ID: | AANLkTikV5ok2tS8t6V+gsAPtE3N6TJq1JpPhMZhG2XL0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2010-08-04 09:44:32 | Re: string_agg delimiter having no effect with order by |
Previous Message | Tom Lane | 2010-08-04 04:55:32 | Re: BUG #5595: Documentation is not installs from VPATH build. |
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2010-08-04 09:44:32 | Re: string_agg delimiter having no effect with order by |
Previous Message | Boxuan Zhai | 2010-08-04 09:23:12 | Re: merge command - GSoC progress |