Two-dimensional array from a list of values (PostgreSQL)

Question

I need to create a two-dimensional array from a list of values. E. g. we have a table with one field called 'Number', and there are 15 records from 1 to 15 in it.

SELECT array_agg(Number) from someTable;
              array_agg
 -------------------------------------
 {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}
(1 row)

How can I aggregate them with SELECT-statement to get a two-dimensional array with fixed width (for example, 4) and unlimited height?

{
 {1,2,3,4},
 {5,6,7,8},
 {9,10,11,12},
 {13,14,15}
}

Thanks.

404 · Accepted Answer · 2018-04-12 15:34:44Z

2

As a workaround to an actual multidimensional array:

With 4 as the fixed width, to agg them in groups of 4:

SELECT ARRAY_AGG(g)
FROM (
        SELECT g, ROW_NUMBER() OVER() - 1 AS r
        FROM generate_series(1, 15) g
) x
GROUP BY r / 4

Then you can just agg that as well if you want to combine all that for whatever reason:

SELECT ARRAY_AGG(a::TEXT)
FROM (
        SELECT ARRAY_AGG(g) AS a
        FROM (
                SELECT g, ROW_NUMBER() OVER() - 1 AS r
                FROM generate_series(1, 15) g
        ) x
        GROUP BY r / 4
) y

Result: {"{1,2,3,4}","{5,6,7,8}","{9,10,11,12}","{13,14,15}"}

And to show it really does work as expected:

SELECT ((ARRAY_AGG(a::TEXT))[2]::INTEGER[])[2]
FROM (
        SELECT ARRAY_AGG(g) AS a
        FROM (
                SELECT g, ROW_NUMBER() OVER() - 1 AS r
                FROM generate_series(1, 15) g
        ) x
        GROUP BY r / 4
) y

Result: 6

edited Apr 12, 2018 at 15:34

answered Apr 12, 2018 at 14:51

404

8,7522 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Two-dimensional array from a list of values (PostgreSQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related