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
Advertisement
Answer
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}