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?

See here: http://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query — Quassnoi, Feb 10 '09 at 17:18

score 105 · Accepted Answer · edited Jul 10 '18 at 13:29

105

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.

edited Jul 10 '18 at 13:29

totymedli

29,531
22
131
165

answered May 04 '11 at 12:09

bobflux

11,123
3
27
27

In PostgreSQL 8.4.8 this returns: {10,2,4,6,8} and {9,7,3,1,5}. I think the above output is from version 9. – SabreWolfy Jul 06 '11 at 07:54
I don't know either why the rows/elements are returned in the order which I see them. I just copied the code and pasted it in to see what it did. – SabreWolfy Jul 07 '11 at 06:27
You can choose the order of the elements in the array - see [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=a2c9f608771055910d958403d60df7b3)! Why the default is the way it is is a mystery... – Vérace Jun 26 '22 at 19:15

score 17 · Answer 2 · answered Feb 10 '09 at 17:26

17

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

answered Feb 10 '09 at 17:26

Johannes Weiss

52,533
16
102
136

I get the following error when trying to create this aggregate function: ERROR: syntax error at or near "(" – SomethingOn Sep 24 '13 at 14:44
Are you using PostgreSQL? – SuN Sep 10 '14 at 08:38

score 13 · Answer 3 · answered Apr 01 '18 at 19:37

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... │
│       ...                                                        │
└───────────┴──────────────────────────────────────────────────────┘

score 4 · Answer 4 · answered Nov 03 '16 at 12:22

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;

Concatenate multiple rows in an array with SQL on PostgreSQL

4 Answers4

Linked