CODEWITHSUNDEEP
arrayspostgresql
p1ka2So
p1ka2So

Reputation: 25

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

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.

Upvotes: 1

Views: 585

Answers (1)

404
404

Reputation: 8582

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

Upvotes: 2

Related Questions