Postgres 14 or later
Use string_to_table() instead of unnest(string_to_array()) for a comma-separated string:
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;
fiddle
Related:
Unnesting an actual array didn't change since Postgres 9.4.
Postgres 9.4 or later
Use WITH ORDINALITY for set-returning functions:
When a function in the FROM clause is suffixed by WITH ORDINALITY, a
bigint column is appended to the output which starts from 1 and
increments by 1 for each row of the function's output. This is most
useful in the case of set returning functions such as unnest().
In combination with the LATERAL feature in pg 9.3+, and according to this thread on pgsql-hackers, the above query can now be written as:
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 preserves all rows from the left table, even if the table expression to the right returns no rows. See:
If that's of no concern you can use the otherwise equivalent, less verbose form with an implicit CROSS JOIN LATERAL:
SELECT t.id, a.elem, a.nr
FROM tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr);
Or simpler, based off an actual array (arr being an array column):
SELECT t.id, a.elem, a.nr
FROM tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr);
Or just go with default column names:
SELECT id, a, ordinality
FROM tbl, unnest(arr) WITH ORDINALITY a;
Or shorter, yet:
SELECT id, a.* FROM tbl, unnest(arr) WITH ORDINALITY a;
Or minimal syntax:
SELECT * FROM tbl, unnest(arr) WITH ORDINALITY a;
The last one returns all columns of tbl, of course.
a is automatically table and column alias (for the first column). The default name of the added ordinality column is ordinality. But it's clearer to add explicit column aliases and table-qualify columns.
The original order of array elements is preserved this way. The manual for unnest():
Expands an array into a set of rows. The array's elements are read out in storage order.
Postgres 8.4 - 9.3
With row_number() OVER (PARTITION BY id ORDER BY elem) you get numbers according to the sort order, not the ordinal number of the original ordinal position in the string.
You can simply omit ORDER BY:
SELECT *, row_number() OVER (PARTITION by id) AS nr
FROM (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl) t;
While this normally works and I have never seen it fail in simple queries, PostgreSQL asserts nothing concerning the order of rows without ORDER BY. It happens to work due to an implementation detail.
To guarantee ordinal numbers of elements in the blank-separated 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;
Or simpler if based off an actual array:
SELECT id, arr[nr] AS elem, nr
FROM (SELECT *, generate_subscripts(arr, 1) AS nr FROM tbl) t;
Related answer on dba.SE:
Postgres 8.1 - 8.4
None of these features are available, yet: RETURNS TABLE, generate_subscripts(), unnest(), array_length(). But this works:
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';
Note in particular, that the array index can differ from ordinal positions of elements. Consider this demo with an extended function:
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';
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;
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
Compare:
