Split and explode columns in Postgresql-CodePudding

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

CodePudding user response：

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

CodePudding user response：

If you want a guarantee that the rows generated from "unnesting" the CSV list are always kept together, it's better to use a single unnest() call:

select t.col1, u.col2, t.col3, u.col4
from the_table t
  cross join lateral unnest(
                      regexp_split_to_array(t.col2, ',\s'),  
                      regexp_split_to_array(t.col4, ',\s')
                     ) as u(col2, col4)

Using string_to_array() is a bit faster as regex processing has some overhead, but you would need to remove the extra spaces:

select t.col1, 
       trim(u.col2) as col2, 
       t.col3, 
       trim(u.col4) as col4
from the_table t
  cross join lateral unnest(
                      string_to_array(t.col2, ','),  
                      string_to_array(t.col4, ',')
                     ) as u(col2, col4)