Normalize array subscripts so they start with 1

Question

PostgreSQL can work with array subscripts starting anywhere.
Consider this example that creates an array with 3 elements with subscripts from 5 to 7:

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

Returns:

[5:7]={1,2,3}

We get the first element at subscript 5:

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

I want to normalize 1-dimensional arrays to start with array subscript 1.
The best I could come up with:

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

The same, easier the read:

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

Do you know a simpler / faster or at least more elegant way?

Benchmark with old solutions on Postgres 9.5

db<>fiddle here

Benchmark including new solution on Postgres 14

db<>fiddle here

Answer 1

There is a simpler method that is ugly, but I believe technically correct: extract the largest possible slice out of the array, as opposed to the exact slice with computed bounds. It avoids the two function calls.

Example:

select ('[5:7]={1,2,3}'::int[])[-2147483648:2147483647];

results in:

  int4   
---------
 {1,2,3}

Answer 2

Eventually, something more elegant popped up with Postgres 9.6. The manual:

It is possible to omit the lower-bound and/or upper-bound of a slice specifier; the missing bound is replaced by the lower or upper limit of the array's subscripts. For example:

So it's simple now:

SELECT my_arr[:];

With my example array literal you need enclosing parentheses to make the syntax unambiguous:

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

About the same performance as Daniel's solution with hard-coded max array subscripts - which is still the way to go with Postgres 9.5 or earlier.

Answer 3

Not sure if this is already covered, but:

SELECT array_agg(v) FROM unnest('[5:7]={1,2,3}'::int[]) AS a(v);

To test performance I had to add id column on the test table. Slow.