PostgreSQL - How to find length of sub array-CodePudding

Trying to get the length of array in array.

RAISE NOTICE '%', a[1][2][1]; -- ok, returns proper value

RAISE NOTICE '%', array_length(a, 1); -- ok, returns array length

But this won't work:

RAISE NOTICE '%', array_length(a[1], 1); --  Error: function array_length(text, integer) does not exist

Why, if a[1] is array?

CodePudding user response：

PostgreSQL's arrays are not arrays of arrays like in some other languages. In PostgreSQL, there are only multidimensional arrays, and it is not easy to take slice. So your question in Postgres has not sense, just there are not sub arrays.

You can take info about the dimensions of the array:

(2022-09-09 18:45:44) postgres=# select array_dims(ARRAY[[[1,2,3],[1,2,3]],[[2,3,4],[1,2,3]]]);
┌─────────────────┐
│   array_dims    │
╞═════════════════╡
│ [1:2][1:2][1:3] │
└─────────────────┘
(1 row)

or you can see the sizes

(2022-09-09 18:46:30) postgres=# select array_length(ARRAY[[[1,2,3],[1,2,3]],[[2,3,4],[1,2,3]]],1);
┌──────────────┐
│ array_length │
╞══════════════╡
│            2 │
└──────────────┘
(1 row)

(2022-09-09 18:47:54) postgres=# select array_length(ARRAY[[[1,2,3],[1,2,3]],[[2,3,4],[1,2,3]]],2);
┌──────────────┐
│ array_length │
╞══════════════╡
│            2 │
└──────────────┘
(1 row)

(2022-09-09 18:47:56) postgres=# select array_length(ARRAY[[[1,2,3],[1,2,3]],[[2,3,4],[1,2,3]]],3);
┌──────────────┐
│ array_length │
╞══════════════╡
│            3 │
└──────────────┘
(1 row)

When you use not enough indexes, then Postgres try to make some slices, but when you don't specify slice correctly, then you get NULL value (this is not the array), and then there cannot be used function array_length:

-- not fully specified cell in array
(2022-09-09 18:55:51) postgres=# select (array[[1,2,3],[4,5,6]])[1];
┌───────┐
│ array │
╞═══════╡
│     ∅ │
└───────┘
(1 row)

(2022-09-09 18:56:14) postgres=# select (array[[1,2,3],[4,5,6]])[1:1];
┌───────────┐
│   array   │
╞═══════════╡
│ {{1,2,3}} │
└───────────┘
(1 row)

(2022-09-09 18:56:21) postgres=# select (array[[1,2,3],[4,5,6]])[1:2];
┌───────────────────┐
│       array       │
╞═══════════════════╡
│ {{1,2,3},{4,5,6}} │
└───────────────────┘
(1 row)

CodePudding user response：

Your slice is bad expressed, example on three dimensional array 2x2x2:

select array_length(('{{{1,2},{3,4}},{{5,6},{7,8}}}'::integer[][][])[1:1],2);

then since the slice is an array of an array you should ask for second dimension.

In the docs https://www.postgresql.org/docs/current/arrays.html