how to make array_agg() work like group_concat() from mySQL

Question

So I have this table:

create table test ( id integer, rank integer, image varchar(30) );

Then some values:

id | rank | image ---+------+------- 1 | 2 | bbb 1 | 3 | ccc 1 | 1 | aaa 2 | 3 | c 2 | 1 | a 2 | 2 | b

I want to group them by id and concatenate the image name in the order given by rank. In mySQL I can do this:

 select id, group_concat( image order by rank asc separator ',' ) from test group by id;

And the output would be:

 1 aaa,bbb,ccc 2 a,b,c

Is there a way I can have this in postgresql?

If I try to use array_agg() the names will not show in the correct order and apparently I was not able to find a way to sort them. (I was using postgres 8.4 )

Jason Weathered · Accepted Answer · 2010-11-01 02:12:22Z

50

In PostgreSQL 8.4 you cannot explicitly order array_agg but you can work around it by ordering the rows passed into to the group/aggregate with a subquery:

SELECT id, array_to_string(array_agg(image), ',') FROM (SELECT * FROM test ORDER BY id, rank) x GROUP BY id;

In PostgreSQL 9.0 aggregate expressions can have an ORDER BY clause:

SELECT id, array_to_string(array_agg(image ORDER BY rank), ',') FROM test GROUP BY id;

edited Nov 1, 2010 at 2:12

answered Oct 31, 2010 at 4:00

Jason Weathered

7,8112 gold badges43 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user491575 Over a year ago

Actually if I add more rows to my test table this solution does not work. Surprisingly enough is not working for me even if I load the table in the correct order. However your solution works in postgresql 8.4 only if I sort the subquery by both id and rank. weird if you ask me

Jason Weathered Over a year ago

The workaround is a bit hacky and it appears to show for you. I suspect there's a query plan difference causing this. I'm somewhat curious as to what your EXPLAIN output is with the bad result. In any case, I've updated my answer with ORDER BY id, rank.

user491575 Over a year ago

First I want to thank you for the answer. It does solve the problem I had.

user491575 Over a year ago

plan for test with 2 indexes: HashAggregate (cost=1.29..1.40 rows=7 width=82) -> Sort (cost=1.17..1.19 rows=7 width=86) Sort Key: test.rank -> Seq Scan on test (cost=0.00..1.07 rows=7 width=86)

user330315 Over a year ago

In Postgres 9.0 you don't need array_agg at all. You can use string_agg(image, ',' order by rank)

|

Collectives™ on Stack Overflow

how to make array_agg() work like group_concat() from mySQL

1 Answer 1

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Linked

Related