How to split the row by a comma and unpivot the table in PostgreSQL?-CodePudding

I have this table and I want to split the column by the comma and unpivot it

The table I have

 ------------ ------------------- --------------------------- 
|birthday_id | child_birthday_id | place                     | 
 ------------ ------------------  --------------------------- 
|  1         |        9          |  Minsk, Mogilev, Polotsk  | 
 ------- ---------- ------------- ---------------------------

and I want to have 3 rows with different places

 ------------ ------------------- ---------------------- 
|birthday_id | child_birthday_id | place                | 
 ------------ ------------------  ---------------------- 
|  1         |        9          |  Mogilev             |
|  1         |        9          |  Minsk               | 
|  1         |        9          |  Polotsk             | 
 ------- ---------- ------------- ----------------------

I know how use in MSSQLS server using CROSS APPLY but in Postgres IDK

SELECT 
    birthday_id , 
    child_birthday_id,
    place
FROM 
    sh.test
    CROSS APPLY STRING_SPLIT(place, ',');

CodePudding user response：

SQL Server's CROSS APPLY translates to CROSS JOIN LATERAL in Postgres. To split a string into a table, you can use regexp_split_to_table().

SELECT t.birthday_id,
       t.child_birthday_id,
       p.place
       FROM sh.test AS t
            CROSS JOIN LATERAL regexp_split_to_table(t.place, '\s*,\s*') AS p
                                                                            (place);

CodePudding user response：

you can use string_to_array() and unnest()

SELECT t.birthday_id , 
       t.child_birthday_id,
       p.place
FROM sh.test t
  CROSS JOIN LATERAL unnest(string_to_array(place, ',')) as p(place);

If you are using Postgres 14 you can also use string_to_table(t.place, ',')