How to access array internal index with postgreSQL?

Question

This is my (perhaps usual for you) non-optimized solution: Workaround for PG problem with non-optimized internal function: ``` CREATE FUNCTION unnest_with_idx(anyarray) RETURNS TABLE(idx integer, val anyelement) AS $$ SELECT generate_series(1,array_upper($1,1)) as idx, unnest($1) as val; $$ LANGUAGE SQL IMMUTABLE; ``` Test: ``` SELECT idx,val from unnest_with_idx(array[1,20,3,5]) as t; ``` But, as I said, non-optimized. I can't believe (!!) that PostgreSQL doesn't have an internal index for arrays ... ? But in this case, the question is how to directly access this index, where the GIN-like internal counter? NOTE1: the solution above and the question is not the same as "how do you create an index by each element of an array?". Also not the same as "Can PostgreSQL index array columns?" because the function is for an isolated array, not for a table index for array fields. NOTE2 (edited after answers): "array indexes" (more popular term) or "array subscripts" or "array counter" are terms…

Accepted Answer

Postgres 9.4 or later

While operating with 1-dimensional arrays and standard index subscripts (like almost always), use the new `WITH ORDINALITY` instead:

```
SELECT t.*
FROM   unnest(ARRAY[1,20,3,5]) WITH ORDINALITY t(val, idx);
```

See:

- PostgreSQL unnest() with element number

Just make sure you don't trip over non-standard subscripts. See:

- Normalize array subscripts so they start with 1

Postgres 9.3 or earlier

(Original answer.)

Postgres does provide dedicated functions to generate array subscripts:

```
WITH   x(a) AS (VALUES ('{1,20,3,5}'::int[]))
SELECT generate_subscripts(a, 1) AS idx
     , unnest(a) AS val
FROM   x;
```

Effectively it does almost the same as @Frank's query, just without subquery. Plus, it also works for subscripts that do not start with `1`.

Either solution works for 1-dimensional arrays only! (Can easily be expanded to multiple dimensions.)

Function:

```
CREATE OR REPLACE FUNCTION unnest_with_idx(anyarray) 
  RETURNS TABLE(idx integer, val anyelement)
  LANGUAGE sql IMMUTABLE AS
$func$
  SELECT generate_subscripts($1, 1), unnest($1);
$func$;
```

Call:

```
SELECT * FROM unnest_with_idx('{1,20,3,5}'::int[]);
```

Also consider:

```
SELECT * FROM unnest_with_idx('[4:7]={1,20,3,5}'::int[]);
```

About custom array subscripts:

- Normalize array subscripts so they start with 1

To get normalized subscripts starting with 1 for a 1-dimensional array:

```
SELECT generate_series(1, array_length($1,1)) ...
```

That's almost the query you had already, just with `array_length()` instead of `array_upper()` - which would fail with non-standard subscripts.

Performance

I ran a quick test on an array of 1000 int with all queries presented here so far. They all perform about the same (~ 3,5 ms) - except for `row_number()` on a subquery (~ 7,5 ms) - as expected, because of the subquery.

How to access array internal index with postgreSQL?

Solution

Problem

Related problems