how to cast each row of type varchar to array?-CodePudding

So I have this problem. I have a table called Orders, where i have a column product_id, but it's of the type varchar, because there can be many of them e.g. '12,3,4,345'. They are all sepearted with ','
But my question is how can I cast in query each row of varchar to array? I tried it with the string_to_array function, but it only casts one row. I need to cast all of the rows in the given table.

My code

SELECT o.product_id, string_to_array(
(select product_id
from orders), ',' ) as array
from orders o ;

I am working in IntellIJ, using JDBC driver on postresql database.

CodePudding user response：

It would be nice if you attach sample data and expected output. So far, I understood your problem, that you want to "put" the contents of each line into an array.

SELECT product_id,
(string_to_array(product_id, ',')::int[]) as array
from orders;

Check out my dbfiddle and let me know if the result is different from what you want.