PostgreSQL multidimensional arrays

Question

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 1

Postgres array elements are always base elements, i.e. non-array types. Sub-arrays are not "elements" in Postgres. Array slices retain original dimensions.

You can either extract a base element, with 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 made 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 a subscript with two array indexes in a 2D 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 1D 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;

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:

• How to select 1d array from 2d array?
• Unnest array by one level