7

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    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 Aug 17 '12 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 Sep 02 '21 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 Sep 02 '21 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 Sep 02 '21 at 18:00
  • @Wellspring: For the record, no parentheses were missing. Not then, not now. I added fiddles to demonstrate. – Erwin Brandstetter Feb 19 '22 at 00:11
  • @ErwinBrandstetter I see that it works in the fiddle. It's been many months since I wrote my comment, so I don't remember what I saw then. What I can say now is that in DBeaver your code still produces a pop-up about "Bind parameters". The dialog lists "array_upper" under Name, and 0 under Value. If I click OK, I get an error "at or near '0'".... whereas if I click Ignore, that works and I get your result fine.With my adjustment, there is no pop-up and I get the result. FWIW... Your support in these forums much appreciated. – Wellspring Feb 20 '22 at 01:36
  • I did just play with my old code, and it *does* look over the top, however. I can still avoid that DBeaver dialog with just this: WITH x(a) AS (SELECT '[5:7]={1,2,3}'::int[]) SELECT a[array_lower(a, 1):(array_upper(a, 1))] FROM x And No, I have no idea why DBeaver has no complaints about array_lower but requires only that I wrap array_upper as shown.... – Wellspring Feb 20 '22 at 01:46
  • Likewise I can simplify the SELECT to this and get no pop-up: 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))] -- maybe it's just a DBeaver thing. – Wellspring Feb 20 '22 at 01:47
  • @Wellspring: Looks like an issue in DBeaver? You might report it. – Erwin Brandstetter Feb 20 '22 at 03:01
  • @ErwinBrandstetter I followed up on your suggestion. https://github.com/dbeaver/dbeaver/issues/15575#issuecomment-1048788950 I guess it was just a DBeaver bug. – Wellspring Feb 23 '22 at 13:48

3 Answers3

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • How to declare without string notation? Something ressambling `select array[1,2,4]::int[0:2] ;` – Peter Krauss May 10 '19 at 21:54
  • @Peter: [The manual:](https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS) `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. – Erwin Brandstetter May 14 '19 at 00:37
  • 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 – sol May 03 '22 at 12:08
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}
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
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.

vyegorov
  • 21,787
  • 7
  • 59
  • 73