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

Standardizing PostgreSQL Array Indices to Begin at 1

PostgreSQL's flexibility allows array indices to start at any number. However, normalizing arrays to begin at index 1 is often beneficial.

Older Method

Prior to Postgres 9.6, a workaround was necessary:

<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>

Simplified Approach (Postgres 9.6 and later)

Postgres 9.6 introduced a more concise and efficient method:

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

For explicit array literals, use parentheses for clarity:

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

This streamlined approach provides performance comparable to the older method, making it the recommended solution for Postgres 9.6 and subsequent versions.

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