I have following table in PostgreSQL. I would like to split some comma separated columns like shown below. I could split the columns using ',' as a separator but how to get the below expected output. The method should be equivalent to pandas explode function.
The input table is shown below. The columns to be exploded are col2 and col4.
col1 col2 col3 col4
0 0, 1, 2 1 a, b, c
1 foo 1
2 1
3 3, 4 1 d, e
The expected output is:
col1 col2 col3 col4
0 0 1 a
0 0 1 b
0 0 1 c
0 1 1 a
0 1 1 b
0 1 1 c
0 2 1 a
0 2 1 b
0 2 1 c
1 foo 1
2 1
3 3 1 d
3 3 1 e
3 4 1 d
3 4 1 e
You can use regexp_split_to_table() for this:
select col1, col2_x, col3, col4_x
from mytable
cross join lateral regexp_split_to_table(col2, ',\s') as s1(col2_x)
cross join lateral regexp_split_to_table(col4, ',\s') as s2(col4_x)
;
db<>fiddle here