PostgreSQL multidimensional arrays
I'm trying to pass data around as a multidimensional array, and I'm getting behavior that seems odd to me. Specifically I'm trying to get a single element out of a 2 dimensional array (so a 1 dimensional array out of my 2 dimension array), and it doesn't work the way I'd expect.
In the following examples #2, 4, & 5 work the way I'd expect, but 1 & 3 do not.
db=> select s.col[2] from (select array[[1,2,3],[4,5,6]] as col) s;
col
-----
(1 row)
db=> select s.col[2:2] from (select array[[1,2,3],[4,5,6]] as col) s;
col
-----
{{4,5,6}}
(1 row)
db=> select array[s.col[2]] from (select array[[1,2,3],[4,5,6]] as col) s;
array
--------
{NULL}
(1 row)
db=> select array[s.col[2:2]] from (select array[[1,2,3],[4,5,6]] as col) s;
array
-------------
{{{4,5,6}}}
(1 row)
db=> select s.col[2][1] from (select array[[1,2,3],[4,5,6]] as col) s;
col
-----
4
(1 row)
Is there doc on this? I have something that's working well enough for me right now, but it's ugly and I worry it won't do the things I want to do next. Technically I'm getting a 2 dimensional array, where 1 dimension only has 1 element. I'd rather just get an array.
I've read (among others):
- http://www.postgresql.org/docs/9.1/static/arrays.html
- http://www.postgresql.org/docs/9.1/static/functions-array.html
- http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
And I'm just not seeing what I'm looking for.
Answer
Postgres array elements are always base elements, i.e. scalar values. Sub-arrays are not "elements" in Postgres. Array slices retain original dimensions.
You can either extract a base element, which is a value of the scalar element data type.
Or you can extract an array slice, which retains the original array data type and also original array dimensions.
Your idea to retrieve a sub-array as "element" would conflict with that and is just not implemented.
The manual might be clearer in its explanation. But at least we can find:
If any dimension is written as a slice, i.e., contains a colon, then all dimensions are treated as slices. Any dimension that has only a single number (no colon) is treated as being from 1 to the number specified. For example,
[2]is treated as[1:2]...
Your 1st example tries to reference a base element, which is not found (you'd need two array indexes in a 2-D array). So Postgres returns NULL.
Your 3rd example just wraps the resulting NULL in a new array.
To flatten an array slice (make it a 1-D array) you can unnest() and feed the resulting set to a new ARRAY constructor. Either in a correlated subquery or in a LATERAL join (requires pg 9.3+). Demonstrating both:
SELECT s.col[2:2][2:3] AS slice_arr
, x.lateral_arr
, ARRAY(SELECT unnest(s.col[2:2][2:3])) AS corr_arr
FROM (SELECT ARRAY[[1,2,3],[4,5,6]] AS col) s
, LATERAL (SELECT ARRAY(SELECT * FROM unnest(s.col[2:2][2:3])) AS lateral_arr) x;
And be sure to read the current version of the manual. your references point to Postgres 9.1, but chances are you are actually using Postgres 9.4.
Related: