Selecting data into a Postgres array

by

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;

More Related Contents:

  1. Return multiple columns of the same row as JSON array of objects
  2. Multiple array_agg() calls in a single query
  3. PostgreSQL unnest() with element number
  4. Aggregate columns with additional (distinct) filters
  5. PostgreSQL: running count of rows for a query ‘by minute’
  6. Check if value exists in Postgres array
  7. Two SQL LEFT JOINS produce incorrect result
  8. Create a pivot table with PostgreSQL
  9. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  10. Normalize array subscripts so they start with 1
  11. How to include “zero” / “0” results in COUNT aggregate?
  12. GROUP BY + CASE statement
  13. Is there something like a zip() function in PostgreSQL that combines two arrays?
  14. Best way to count rows by arbitrary time intervals
  15. Compute percents from SUM() in the same SELECT sql query
  16. Get the distinct sum of a joined table column
  17. Aggregate a single column in query with many columns
  18. PostgreSQL JOIN with array type with array elements order, how to implement?
  19. How to store array or multiple values in one column
  20. Postgres window function and group by exception
  21. Count cumulative total in Postgresql
  22. Check if NULL exists in Postgres array
  23. Truncate timestamp to arbitrary intervals
  24. Any downsides of using data type “text” for storing strings?
  25. Change PostgreSQL columns used in views
  26. How to set auto increment primary key in PostgreSQL?
  27. PostgreSQL IF statement
  28. Postgres not using index when index scan is much better option
  29. convert Postgres geometry format to WKT
  30. How to detect query which holds the lock in Postgres?

Leave a Comment