PostgreSQL unnest() Function and element number

question

When a column containing separated values ​​is encountered, the unnest() function provides a way to extract these values:

myTable
id | elements
--- ------------
1  |ab,cd,efg,hi
2  |jk,lm,no,pq
3  |rstuv,wxyz

select id, unnest(string_to_array(elements, ',')) AS elem
from myTable

id | elem
--- -----
1  | ab
1  | cd
1  | efg
1  | hi
2  | jk
...

However, you may want to include element numbers as well, in the format as follows:

id | elem | nr
--- ------ ---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...

The ultimate goal is to get the original position of each element in the source string without using window functions such as row_number() or rank()), because these functions always return 1, which is probably because all elements are in the same row in the source table.

Solution

PostgreSQL 14 or later

For comma-separated strings, use string_to_table() instead of unnest(string_to_array()):

SELECT t.id, a.elem, a.nr
FROM   tbl t
LEFT   JOIN LATERAL string_to_table(t.elements, ',') WITH ORDINALITY AS a(elem, nr) ON true

Demo

PostgreSQL 9.4 or later

For functions that return sets, use WITH ORDINALITY:

SELECT t.id, a.elem, a.nr
FROM   tbl AS t
LEFT   JOIN LATERAL unnest(string_to_array(t.elements, ',')) WITH ORDINALITY AS a(elem, nr) ON true

LEFT JOIN ... ON true Make sure all rows in the left table are preserved regardless of whether the right table expression returns any rows.

Or, since LEFT JOIN ... ON true retains all rows, a cleaner query version can be used:

SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr)

For actual arrays (arr is an array column), a simpler form can be used:

SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr)

For simplicity, the default column name can be used:

SELECT id, a, ordinality
FROM   tbl, unnest(arr) WITH ORDINALITY a

can also be further simplified:

SELECT * FROM tbl, unnest(arr) WITH ORDINALITY a

This final form returns all columns of tbl. Of course, explicitly specifying column alias and table-qualified columns can improve clarity.

a is used as both a table alias and a column alias (for the first column), and the default name of the attached sequence column is ordinality.

PostgreSQL 8.4 - 9.3

Use row_number() OVER (PARTITION BY id ORDER BY elem) Get the number according to the sort order (not the number position):

SELECT *, row_number() OVER (PARTITION by id) AS nr
FROM  (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl) t

While this is generally valid and no failures are observed in simple queries, PostgreSQL does not guarantee the order of rows without ORDER BY . The current behavior is the result of implementation details.

To ensure that the space separates the sequence number of the element in the string : : ]

SELECT id, arr[nr] AS elem, nr
FROM  (
   SELECT *, generate_subscripts(arr, 1) AS nr
   FROM  (SELECT id, string_to_array(elements, ' ') AS arr FROM tbl) t
   ) sub

actual array , a simpler version can be used:

SELECT id, arr[nr] AS elem, nr
FROM  (SELECT *, generate_subscripts(arr, 1) AS nr FROM tbl) t

RETURNS TABLE, generate_subscripts(), unnest() and array_length()], you can use a custom SQL function called f_unnest_ord:

CREATE FUNCTION f_unnest_ord(anyarray, OUT val anyelement, OUT ordinality integer)
  RETURNS SETOF record
  LANGUAGE sql IMMUTABLE AS
'SELECT $1[i], i - array_lower($1,1)   1
 FROM   generate_series(array_lower($1,1), array_upper($1,1)) i'

CREATE FUNCTION f_unnest_ord_idx(anyarray, OUT val anyelement, OUT ordinality int, OUT idx int)
  RETURNS SETOF record
  LANGUAGE sql IMMUTABLE AS
'SELECT $1[i], i - array_lower($1,1)   1, i
 FROM   generate_series(array_lower($1,1), array_upper($1,1)) i'

f_unnest_ord_idx returns the attached idx column. Compare:

SELECT id, arr, (rec).*
FROM  (
   SELECT *, f_unnest_ord_idx(arr) AS rec
   FROM  (
      VALUES
        (1, '{a,b,c}'::text[])  --  short for: '[1:3]={a,b,c}'
      , (2, '[5:7]={a,b,c}')
      , (3, '[-9:-7]={a,b,c}')
      ) t(id, arr)
   ) sub

Output]

 id | arr | val | ordinality | idx
---- ----------------- ----- ------------ -----
  1 | {a,b,c} | a | 1 | 1
  1 | {a,b,c} | b | 2 | 2
  1 | {a,b,c} | c | 3 | 3
  2 | [5:7]={a,b,c} | a | 1 | 5
  2 | [5:7]={a,b,c} | b | 2 | 6
  2 | [5:7]={a,b,c} | c | 3 | 7
  3 | [-9:-7]={a,b,c} | a | 1 | -9
  3 | [-9:-7]={a,b,c} | b | 2 | -8
  3 | [-9:-7]={a,b,c} | c | 3 | -7