Is there any functional difference between array_upper and array_length?

Question

The postgres docs on these 2 array functions are pretty weak.

I've tried both functions a few different ways and they seem to return the same results.

SELECT array_length(array[[1, 2], [3, 4], [5, 6]], 1);
SELECT array_upper(array[[1, 2], [3, 4], [5, 6]], 1);

SELECT array_length(array[[1, 2], [3, 4], [5, 6]], 2);
SELECT array_upper(array[[1, 2], [3, 4], [5, 6]], 2);

Answer 1

Yes, there is a difference. PostgreSQL array subscripts start at one by default but they don't have to:

By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].
[...]
Subscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to myarray[-2:7] to create an array with subscript values from -2 to 7.
[...]
By default, the lower bound index value of an array's dimensions is set to one. To represent arrays with other lower bounds, the array subscript ranges can be specified explicitly before writing the array contents.

In general, you need to use array_lower and array_upper instead of assuming that the array will start at 1 and end at array_length(a, n).