How to aggregate two PostgreSQL columns to an array separated by brackets

sql arrays postgresql concatenation array-agg

Create an array from the two columns, the aggregate the array:

select id, array_agg(array[x,y])from the_tablegroup by id;

Note that the default text representation of arrays uses curly braces ( {..}) not square brackets ([..])

sql arrays postgresql concatenation array-agg

In Postgres 9.5 or later array_agg() takes arrays as input to allow the simple syntax provided by @a_horse:

SELECT id, array_agg(ARRAY[x, y]) AS xyFROM   Table1GROUP  BY id;

In older versions, this isn't implemented yet. You can create your own aggregate function (once) to achieve the same:

CREATE AGGREGATE array_agg_mult (anyarray)  (    SFUNC     = array_cat  , STYPE     = anyarray  , INITCOND  = '{}');

Then:

SELECT id, array_agg_mult(ARRAY[ARRAY[x,y]]) AS xy  -- note the 2D arrayFROM   Table1GROUP  BY id;

Details:

Or you can concatenate a string:

SELECT id, '[[' || string_agg(concat_ws(',', x, y), '],[') || ']]' AS xyFROM   Table1GROUP  BY id;

Produces your desired result exactly. A string, not an array.

CodeHunter