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

Slicing (as @DanielVérité suggested) was the first thing that came to mind for me. Of course you could get the fastest transformation by writing a C function, although it might be pretty close to the slicing timing. The only other alternative that comes to mind would be to cast the array to text, parse out the substring to the right of the =, and cast that back to an array of the right type. And I'm pretty sure that's uglier and more fragile than the slicing. — kgrittn
– kgrittn, Commented Aug 17, 2012 at 20:47
Erwin has posted the newer, more elegant solution for newer versions. But... for anyone playing with code here (for learning purposes only), some code posted here doesn't run (anymore... if it ever did) as provided. Edit queue is full, so I'll provide corrections here over several comments, for easier reading. What's missing is a set of parentheses around a few key values. At least, that's my experience, running these queries in dbeaver. — Wellspring
– Wellspring, Commented Sep 2, 2021 at 18:00
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))] — Wellspring
– Wellspring, Commented Sep 2, 2021 at 18:00
WITH x(a) AS ( SELECT '[5:7]={1,2,3}'::int[] ) SELECT a[((array_lower(a, 1))):((array_upper(a, 1)))] FROM x — Wellspring
– Wellspring, Commented Sep 2, 2021 at 18:00
@Wellspring: For the record, no parentheses were missing. Not then, not now. I added fiddles to demonstrate. — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 19, 2022 at 0:11

Erwin Brandstetter · Accepted Answer · 2022-03-18 16:27:15Z

7

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.

edited Mar 18, 2022 at 16:27

answered Jun 9, 2017 at 2:12

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Peter Krauss Over a year ago

How to declare without string notation? Something ressambling select array[1,2,4]::int[0:2] ;

Erwin Brandstetter Over a year ago

@Peter: The manual: The subscripts of an array value built with ARRAY always begin with one. But there are ways with subscripted assignments. Ask a new question if details are of interest.

sol Over a year ago

example in the case when casting oidvector to oid[] ... the array index starts with 0 ... so i needed to use : ... (proargtypes::oid[])[:] = (__in_regtypes_::oid[])[:] ... in a comparison i was making with pg_proc info

Daniel Vérité · Accepted Answer · 2012-08-17 19:49:36Z

6

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}

answered Aug 17, 2012 at 19:49

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

3 Comments

ErikE Over a year ago

Actually, I have 2147483647 memorized... using it seems quite the simple answer, to me. 4294967295, too. :)

Erwin Brandstetter Over a year ago

This is the best answer - unless something more elegant pops up.

Erwin Brandstetter Over a year ago

Eventually, something more elegant popped up with Postgres 9.6. See added answer.

vyegorov · Accepted Answer · 2012-08-17 22:09:48Z

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.

edited Aug 17, 2012 at 22:09

answered Aug 17, 2012 at 21:57

vyegorov

23k7 gold badges61 silver badges76 bronze badges

1 Comment

Peter Krauss Over a year ago

How to declare without string notation? Something ressambling select array[1,2,4]::int[0:2] ;

Collectives™ on Stack Overflow

Normalize array subscripts so they start with 1

Benchmark with old solutions on Postgres 9.5

Benchmark including new solution on Postgres 14

3 Answers 3

3 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Benchmark with old solutions on Postgres 9.5

Benchmark including new solution on Postgres 14

3 Answers 3

3 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related