Postgres: Split column values & transpose

I have a table like this:

category_id | product_id
---------------------------
1           | 1;2;3;4
2           | 7;8;9
3           | 6

And I want to convert it to this:

category_id | product_id
---------------------------
1           | 1
1           | 2
1           | 3
1           | 4
2           | 7
2           | 8
2           | 9
3           | 6

I have tried splitting the column based on ; but the product_id count varies based on the category_id. Is there anyway I can achieve this?

Answer

You can use PostgreSQL’s array manipulation functions:

SELECT category_id, unnest(string_to_array(product_id, ';')) FROM test;

string_to_array does exactly what it says — splits a string into an array of parts using a supplied delimiter, then unnest is used to separate an array value into multiple rows containing elements from the array.