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.
This question is related to
sql
arrays
postgresql
window-functions
set-returning-functions
Try:
select v.*, row_number() over (partition by id order by elem) rn from
(select
id,
unnest(string_to_array(elements, ',')) AS elem
from myTable) v
If the order of element is not important, you can
select
id, elem, row_number() over (partition by id) as nr
from (
select
id,
unnest(string_to_array(elements, ',')) AS elem
from myTable
) a
unnest2()
as exerciseOlder versions before pg v8.4 need a user-defined unnest()
. We can adapt this old function to return elements with an index:
CREATE FUNCTION unnest2(anyarray)
RETURNS setof record AS
$BODY$
SELECT $1[i], i
FROM generate_series(array_lower($1,1),
array_upper($1,1)) i;
$BODY$ LANGUAGE sql IMMUTABLE;
Use Subscript Generating Functions.
http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS
For example:
SELECT
id
, elements[i] AS elem
, i AS nr
FROM
( SELECT
id
, elements
, generate_subscripts(elements, 1) AS i
FROM
( SELECT
id
, string_to_array(elements, ',') AS elements
FROM
myTable
) AS foo
) bar
;
More simply:
SELECT
id
, unnest(elements) AS elem
, generate_subscripts(elements, 1) AS nr
FROM
( SELECT
id
, string_to_array(elements, ',') AS elements
FROM
myTable
) AS foo
;
Source: Stackoverflow.com