PostgreSQL multidimensional arrays
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_arrFROM (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: