How Can I Normalize Array Subscript Ranges in PostgreSQL 9.6 and Later?

Normalization of array subscript ranges in PostgreSQL 9.6 and later

Array subscripts in PostgreSQL can start from any index. However, many applications prefer uniformly indexed arrays starting at 1. This article discusses methods for normalizing one-dimensional arrays to follow this convention.

Existing solutions

Initially, a complex solution using the array_lower and array_upper functions was used to normalize the subscripts. These methods, while effective, are complex and inelegant.

Simplification for PostgreSQL 9.6

With the release of PostgreSQL 9.6 comes a more straightforward solution. The manual describes the ability to omit the lower and/or upper bound of the slice specifier, which then defaults to the minimum or maximum subscript of the array.

Example

To normalize an array literal with non-uniform subscripts:

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

In our example, parentheses are needed to resolve ambiguity:

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

This simplified syntax achieves similar performance to earlier solutions. For earlier PostgreSQL versions (9.5 or earlier), it is still recommended to use array_lower and array_upper and a fixed maximum subscript.

The above is the detailed content of How Can I Normalize Array Subscript Ranges in PostgreSQL 9.6 and Later?. For more information, please follow other related articles on the PHP Chinese website!