Concatenate multiple rows in an array with SQL on PostGreSQL

Question

Hello,

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 ?

Thank you very much.

Answer 1

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