Selecting arrays in nicely printed format in SQL

Question

I'm trying to select for a two dimensional array of integers, and directing the output to a file. Is there any way that I can write a postgresql statement that would make the output of the select statement nicely formatted. As in each array of integers that is an element of the 2D array is on its own line.

Right now I just get this output:

SELECT array FROM table LIMIT 1;
{{0,0,0},{1,1,1},{2,2,2},{3,3,3},{0,0,0},{1,1,1},{2,2,2},{3,3,3}
,{0,0,0},{1,1,1},{2,2,2},{3,3,3},{0,0,0},{1,1,1},{2,2,2},{3,3,3}
,{0,0,0},{1,1,1},{2,2,2},{3,3,3},{0,0,0},{1,1,1},{2,2,2},{3,3,3}}

And I would like to get something more like this:

{0,0,0}
{1,1,1}
{2,2,2}
...

I can do this after the query returns with some parsing, but if its possible to do it in Postgres itself that would be ideal.

+1 Thank you for a very clear question with a working example and demo output. Makes answering so much easier! — Erwin Brandstetter, Sep 29 '11 at 23:27
unless you are using composite datatypes in postgresql (like `array`), it really would be better to do this in application code rather than tie up the database with string parsing, especially if you will be using the whole column value for final output. — SingleNegationElimination, Sep 29 '11 at 23:33
On the other hand, postgres itself is pretty well versed in string handling. So it is certainly an option in some cases. — Erwin Brandstetter, Sep 30 '11 at 00:19

Erwin Brandstetter · Accepted Answer · 2011-09-30T00:12:23.687

There are several ways. One way is to cast the array to text and split it up with regexp_split_to_table().
This function is present in PostgreSQL 8.3 or later.

SELECT regexp_split_to_table(trim(my_2d_intarr::text, '{}'), '},{');

Output:

0,0,0
1,1,1
2,2,2

If you want the enclosing brackets (maybe you don't?), add them back like this:

SELECT '{' || regexp_split_to_table(trim(my_2d_intarr::text, '{}'), '},{') || '}';

Ourtput:

{0,0,0}
{1,1,1}
{2,2,2}

Alternative:

This should also work with PostgreSQL 8.2 or maybe even earlier, but I did not test that.

SELECT my_2d_int_arr_var[x:x][1:3]
  FROM (SELECT generate_series(1, array_upper(my_2d_intarr, 1), 1)::int4 AS x)) x

Output:

{{0,0,0}}
{{1,1,1}}
{{2,2,2}}

_{(You may want to strip some curly brackets ..)}

Else, I would write a plpgsql function that loops through the array. Fairly easy.

There is also the related unnest() function, but it returns a row per base element (integer in this case), so it's no use here.

One (fast!) way to output the result: COPY.

Selecting arrays in nicely printed format in SQL

1 Answers1

Alternative:

Linked