Normalize array subscripts for 1-dimensional array so they start with 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}
Eventually, something more elegant popped up with Postgres 9.6. The manual:
It is possible to omit the
lower-bound
and/orupper-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.
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.