PostgreSQL UNNEST Array and Insert Into New Table-CodePudding

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