PostgreSQL Multidimensional Arrays

Question

I am trying to pass data as a multidimensional array, and I am getting behavior that seems strange to me. In particular, I am trying to get one element from a 2-dimensional array (therefore a 1-dimensional array from my 2-dimensional array), and it does not work as I expected.

In the following examples, # 2, 4, and 5 work as I expected, but 1 and 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 a document on this? I have something that works well enough for me right now, but it's ugly, and I'm worried that he will not do what I want to do next. Technically, I get a 2-dimensional array, where 1 size has only 1 element. I would rather just get an array.

I read (among other things):

• 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

, .

Answer 1

Postgres , . - "" Postgres. .

, .
, , .

- "" .

. :

, , . , ( ) 1 . , [2] [1:2]...

, ( 2-D ). Postgres NULL.
NULL .

( 1- ), unnest() ARRAY. , LATERAL ( pg 9.3+). :

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;

. Postgres 9.1, , , Postgres 9.4.

:

• 1d 2d postgresql