When I have a column with separated values, I can use the unnest()
function:
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
...
How can I include element numbers? I.e.:
id | elem | nr
---+------+---
1 | ab | 1
1 | cd | 2
1 | efg | 3
1 | hi | 4
2 | jk | 1
...
I want the original position of each element in the source string. I've tried with window functions (row_number()
, rank()
etc.) but I always get 1
. Maybe because they are in the same row of the source table?
I know it's a bad table design. It's not mine, I'm just trying to fix it.
Best Answer
Postgres 9.4 or later
Use
WITH ORDINALITY
for set-returning functions: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:LEFT JOIN ... ON TRUE
preserves all rows in the left table, even if the table expression to the right returns no rows. If that's of no concern you can use this otherwise equivalent, less verbose form with an implicitCROSS JOIN LATERAL
:Or simpler if based off an actual array (
arr
being an array column):Or even, with minimal syntax:
a
is automatically table and column alias. The default name of the added ordinality column isordinality
. But it's better (safer, cleaner) to add explicit column aliases and table-qualify columns.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
: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:
Or simpler if based off an actual array:
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:Note in particular, that the array index can differ from ordinal positions of elements. Consider this demo with an extended function:
Compare: