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:

SELECT '[5:7]={1,2,3}'::int[];

Accessing the first element requires using index 5:

SELECT ('[5:7]={1,2,3}'::int[])[5];

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

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)];

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

WITH cte(a) AS (SELECT '[5:7]={1,2,3}'::int[])
SELECT a[array_lower(a, 1):array_upper(a, 1)]
FROM cte;

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

SELECT my_arr[:];

For our example, parentheses improve readability:

SELECT ( '[5:7]={1,2,3}'::int[] )[:];

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!