Selecting data into a Postgres array

Question

I have the following data:

name          id             url

John          1              someurl.com
Matt          2              cool.com
Sam           3              stackoverflow.com

How can I write an SQL statement in Postgres to select this data into a multi-dimensional array, i.e.:

{{John, 1, someurl.com}, {Matt, 2, cool.com}, {Sam, 3, stackoverflow.com}}

I've seen this kind of array usage before in Postgres but have no idea how to select data from a table into this array format.

Assuming here that all the columns are of type text.

score 22 · Accepted Answer · edited May 23 '17 at 12:25

You cannot use array_agg() to produce multi-dimensional arrays, at least not up to PostgreSQL 9.4.
(But the upcoming Postgres 9.5 ships a new variant of array_agg() that can!)

What you get out of @Matt Ball's query is an array of records (the_table[]).

An array can only hold elements of the same base type. You obviously have number and string types. Convert all columns (that aren't already) to text to make it work.

You can create an aggregate function for this like I demonstrated to you here before.

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

Call:

SELECT array_agg_mult(ARRAY[ARRAY[name, id::text, url]]) AS tbl_mult_arr
FROM   tbl;

Note the additional ARRAY[] layer to make it a multidimensional array (2-dimenstional, to be precise).

Instant demo:

WITH tbl(id, txt) AS (
    VALUES
      (1::int, 'foo'::text)
     ,(2,      'bar')
     ,(3,      '}b",') -- txt has meta-characters
    )
    , x AS (
    SELECT array_agg_mult(ARRAY[ARRAY[id::text,txt]]) AS t
    FROM   tbl
    )
SELECT *, t[1][3] AS arr_element_1_1, t[3][4] AS arr_element_3_2
FROM   x;

Matt Ball · Answer 2 · 2012-08-01T15:33:14.337

8

You need to use an aggregate function; array_agg should do what you need.

SELECT array_agg(s) FROM (SELECT name, id, url FROM the_table ORDER BY id) AS s;

edited Aug 01 '12 at 15:33

answered Aug 01 '12 at 15:25

Matt Ball

354,903
100
647
710

1

This answer returns an array of records, not a multi-dimensional array. – Erwin Brandstetter Aug 01 '12 at 15:52
Yes - just got in working in context and you're right... I really need the multi-dimensional array! – harman_kardon Aug 01 '12 at 16:01
1

@MattW: I think you can be helped. :) Posted an answer. – Erwin Brandstetter Aug 01 '12 at 16:07

Selecting data into a Postgres array

2 Answers2

Linked