Skip to content
Advertisement

Normalize array subscripts so they start with 1

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

Returns:

We get the first element at subscript 5:

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

The same, easier the read:

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:

results in:

  int4   
---------
 {1,2,3}
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement