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?
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.
Postgres has a function regexp_split_to_table() that does exactly what you want -- split a string into multiple rows I prefer to be explicit about my lateral joins, so I would write this as:
select t.category_id, r.product_id
from t cross join lateral
regexp_split_to_table(t.product_id, ';') r(product_id);
However, if you prefer conciseness, you can write this as:
select t.category_id, regexp_split_to_table(t.product_id, ';') as product_id
from t;
Here is a db<>fiddle.
