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.