sql - Inconsistent behavior when slicing a 2d array in PostgreSQL

Question

Let's say I have a 2d array:

# SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
     array     
---------------
 {{1,2},{3,4}}
(1 row)

Now, if I want to get the first element of each inner array, adding (...)[:][1] will do the trick:

# SELECT (ARRAY[ARRAY[1,2], ARRAY[3,4]])[:][1];
   array   
-----------
 {{1},{3}}
(1 row)

BUT: If I want to obtain the second element of each inner array, I have to opt for adding (...)[:][2:2], as (...)[:][2] would return the untouched array again

# SELECT (ARRAY[ARRAY[1,2], ARRAY[3,4]])[:][2];
     array     
---------------
 {{1,2},{3,4}}
(1 row)

# SELECT (ARRAY[ARRAY[1,2], ARRAY[3,4]])[:][2:2];
   array   
-----------
 {{2},{4}}
(1 row)

What is the reason for this inconsistent behavior?

question from:https://stackoverflow.com/questions/65946491/inconsistent-behavior-when-slicing-a-2d-array-in-postgresql

Answer 1

I think the documentation explains this pretty well:

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.

That is, when you are using slices, Postgres expects all dimensions to be slices. Those that are not are defaulted to 1:n.