Array_agg in postgres

12 September 2014

When you need to group your data, but you need to know what are these data in these group. array_agg is a convenient aggregation function to help achieve this goal.

For example you want to group books by category, and you need to know which books in the group, this is how we get it.

create table books (
    id serial primary key,
    category text,
    title text
);

insert into books(category, title) values
( 'computer', 'Ruby'),
( 'art', 'Art'),
( 'computer', 'Rails')
;
select category, array_agg(title) as titles from books group by category;
+----------+------------+
| category | title      |
|----------|------------|
| art      | Art        |
| computer | Ruby,Rails |
+----------+------------+

You can play with it on

You could see a columns titles contain a title array in each group.