PostgreSQL UNNEST Array and Insert Into New Table

Question

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

It's unclear whether those single quotes are real - at either stage. Please provide a complete setup with CREATE TABLE (showing data type and constraints) and INSERT statements (showing exact values). — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 4, 2022 at 23:49

Mike Organek · Accepted Answer · 2022-08-04 23:41:26Z

1

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

answered Aug 4, 2022 at 23:41

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dex Over a year ago

That works great! What does u(ary) do?

Mike Organek Over a year ago

The u(ary) aliases the result of the unnest(col1) as table u with one column, ary. Without that aliasing, the result of the unnest is called unnest instead of ary.

Collectives™ on Stack Overflow

PostgreSQL UNNEST Array and Insert Into New Table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related