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

Simplified method for normalizing PostgreSQL array subscripts

PostgreSQL allows array subscripts to start from any value. However, in some cases it is necessary to normalize a one-dimensional array to start at index 1.

The previous solution used the array_lower() and array_upper() functions to specify the desired 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>

While this approach works, a more elegant solution has emerged in PostgreSQL 9.6. The documentation now allows omitting lower and/or upper bounds for slice specifiers. If omitted, missing bounds will default to the lowest or highest array index.

With this feature, the normalization process becomes quite simple:

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

In the example provided, brackets ensure the syntax is clear:

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

This optimized solution performs similarly to the previous generation solution, making it the preferred method for PostgreSQL 9.6 and later. For older versions (9.5 and earlier), hardcoding the maximum array subscript is still the recommended approach.

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