Concatenate multiple rows in an array with SQL on PostgreSQL

Question

I have a table constructed like this :

oid | identifier | value
1   | 10         | 101
2   | 10         | 102
3   | 20         | 201
4   | 20         | 202
5   | 20         | 203

I'd like to query this table to get a result like this :

identifier | values[]
10         | {101, 102}
20         | {201, 202, 203}

I can't figure a way to do that.
Is that possible? How?

Answer 1

This is a Postgres built-in since a few versions so you no longer need to define your own, the name is array_agg().

test=> select array_agg(n) from generate_series(1,10) n group by n%2;
  array_agg   
--------------
 {1,3,5,7,9}
 {2,4,6,8,10}

(this is Postgres 8.4.8).

Note that no ORDER BY is specified, so the order of the result rows depends on the grouping method used (here, hash) ie, it is not defined. Example:

test=> select n%2, array_agg(n) from generate_series(1,10) n group by (n%2);
 ?column? |  array_agg   
----------+--------------
        1 | {1,3,5,7,9}
        0 | {2,4,6,8,10}

test=> select (n%2)::TEXT, array_agg(n) from generate_series(1,10) n group by (n%2)::TEXT;
 text |  array_agg   
------+--------------
 0    | {2,4,6,8,10}
 1    | {1,3,5,7,9}

Now, I don't know why you get {10,2,4,6,8} and {9,7,3,1,5}, since generate_series() should send the rows in order.

Answer 2

Simple example: each course have many lessons, so if i run code below:

SELECT
  lessons.course_id AS course_id,
  array_agg(lessons.id) AS lesson_ids
FROM lessons
GROUP BY
  lessons.course_id
ORDER BY
  lessons.course_id

i'd get next result:

┌───────────┬──────────────────────────────────────────────────────┐
│ course_id │                   lesson_ids                         │
├───────────┼──────────────────────────────────────────────────────┤
│         1 │ {139,140,141,137,138,143,145,174,175,176,177,147,... │
│         3 │ {32,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,... │
│         5 │ {663,664,665,649,650,651,652,653,654,655,656,657,... │
│         7 │ {985,984,1097,974,893,971,955,960,983,1045,891,97... │
│       ...                                                        │
└───────────┴──────────────────────────────────────────────────────┘

Answer 3

You have to create an aggregate function, e.g.

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

then

SELECT identifier, array_accum(value) AS values FROM table GROUP BY identifier;

HTH

Answer 4

Here is the code for the requested output.

select identifier, array_agg(value)
from (
  values
    (1   , 10         , 101),
    (2   , 10         , 102),
    (3   , 20         , 201),
    (4   , 20         , 202),
    (5   , 20         , 203)
  ) as tab (oid, identifier, value)
group by identifier
order by identifier;