Selecting data into a Postgres array

aggregate-functions, arrays, postgresql, sql

Solution

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;

Problem

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`.

Original source

Related problems