CODEWITHSUNDEEP
sqlarrayspostgresqlaggregate-functions
harman_kardon
harman_kardon

Reputation: 1717

Selecting data into a Postgres array

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.

Upvotes: 13

Views: 22066

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656241

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;

Upvotes: 22

Matt Ball
Matt Ball

Reputation: 359776

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;

Upvotes: 8

Related Questions