I have an array column I want to unnest, split the element values, and copy into another table. For example:
id | col1
-----------------
1 | '{"a:1", "b:2"}'
I'd like to insert into a new table that looks like:
table1_id | col1 | col2
------------------------
1 | 'a' | 1
1 | 'b' | 2
CodePudding user response:
You can issue an insert
from this select
:
select id as table1_id,
(string_to_array(ary, ':'))[1] as col1,
(string_to_array(ary, ':'))[2] as col2
from table1
cross join lateral unnest(col1) as u(ary);
db<>fiddle here