Why does it seems that matrices do not return an individual array in postgreSQL?

Question

I've been trying to select an individual array within a matrix in postgre, but with no success. I don't know why this happens. I already tried to recreate in different ways both the matrix and the query, being able to get "{{array_content}}" via slices (say matrix[2:2]), but not "{array_content}". Here is an example of when it return a table without content :

WITH matrix_test AS (
  SELECT '{{1,2,3},{4,5,6},{7,8,9}}'::integer[][] AS matrix
)
SELECT matrix[2] AS second_line FROM matrix_test;

Is this behavior actually expected? Is it possible to get that target format?

Answer 1

You're trying to extract a slice and you omitted the whole 2nd dimension. The right syntax is:
_{demo at db<>fiddle}

WITH matrix_test AS (
  SELECT '{{1,2,3},{4,5,6},{7,8,9}}'::integer[][] AS matrix
)
SELECT matrix[2:2][:] AS second_line FROM matrix_test;

[2:2] means from 2 to 2 (only 2) in the 1st dimension and [:] means everything in the 2nd dimension. Or, only column 2, and all rows in it, if that's how you interpret the matrix or a nested/multidimensional array.

PostgreSQL arrays can be counter-intuitive, but as long as you explicitly specify all slice bounds for all dimensions, you should be fine. The reason why a plain matrix[2] didn't work can be found in 8.15.3. Accessing Arrays:

An array subscript expression will return null if either the array itself or any of the subscript expressions are null. Also, null is returned if a subscript is outside the array bounds (this case does not raise an error). For example, if schedule currently has the dimensions [1:3][1:2] then referencing schedule[3][3] yields NULL. Similarly, an array reference with the wrong number of subscripts yields a null rather than an error.