How Can I Efficiently Normalize PostgreSQL Array Subscripts to Start at 1?

PostgreSQL array subscript normalization: starting from 1

PostgreSQL array subscripts can start from any value. However, array operations can be simplified by normalizing the subscripts of one-dimensional arrays to start at 1. This article explores a more elegant method of normalization introduced in PostgreSQL version 9.6.

In versions of PostgreSQL prior to 9.6, one approach was to use the array_lower and array_upper functions to specify a subscript range:

<code class="language-sql">SELECT ('[5:7]={1,2,3}'::int[])[array_lower('[5:7]={1,2,3}'::int[], 1):array_upper('[5:7]={1,2,3}'::int[], 1)]</code>

However, starting with PostgreSQL 9.6, a simpler and more efficient method has emerged: omitting the lower and upper bounds of the slice specifier. According to the manual:

<code>可以省略切片指定符的下限和/或上限；缺失的边界将被替换为数组下标的下限或上限。</code>

This allows the use of a concise normalization syntax:

<code class="language-sql">SELECT my_arr[:];</code>

For example, for the array literal '[5:7]={1,2,3}', you can use:

<code class="language-sql">SELECT ('[5:7]={1,2,3}'::int[])[:];</code>

This approach has similar performance to the previous approach of using hard-coded maximum array subscripts, but it is more elegant and can be used with arrays of any size.

The above is the detailed content of How Can I Efficiently Normalize PostgreSQL Array Subscripts to Start at 1?. For more information, please follow other related articles on the PHP Chinese website!