How to Renormalize PostgreSQL Array Subscripts to Start from 1?

Adjusting PostgreSQL Array Indices to Start from 1

PostgreSQL's flexibility allows for arrays with indices that don't begin at the standard 1. For example, the following array's elements are indexed from 5 to 7:

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

Accessing the first element requires using index 5:

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

However, many users prefer arrays with indices starting at 1. Here's how to achieve this:

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

This extracts the array slice from the lowest to highest index, effectively resetting the indexing.

A more readable approach using a Common Table Expression (CTE):

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

For PostgreSQL 9.6 and later, a simplified method exists: omitting the lower and upper bounds in the slice specification:

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

For our example, parentheses improve readability:

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

This approach is efficient and works well even in Postgres versions prior to 9.6.

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