arrays - array_upper 和 array_length 之间有什么功能上的区别吗？

Question

关于这两个数组函数的 postgres 文档非常薄弱。

我以几种不同的方式尝试了这两个函数，它们似乎返回了相同的结果。

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

是的，有区别。 PostgreSQL 数组下标默认从 1 开始，但 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.

一般情况下，需要使用array_lower and array_upper而不是假设数组将从 1 开始并在 array_length(a, n) 结束。