Insert a row for each array item -- for many arrays

Question

Say you have a table:

CREATE TABLE tab (
x  integer,
y  integer,
);

And multiple rows of the form x, [y1, y2, ...]:

x ys
1 [2,3]
4 [5,6,7]

There might be an arbitrary but non-zero (or non-nil) amount of ys in each row. x always non-nil.

How to insert such that you end up with a row for each x, y pair, that is:

@a_horse_with_no_name by manual specification in the SQL statement — tsorn
– tsorn, Commented May 20, 2020 at 11:36

user330315 · Accepted Answer · 2020-05-20 11:37:49Z

3

You can unnest() the arrays:

insert into tab (x,y)
select v.x, t.y
from (
 values 
   (1, array[2,3]), 
   (4, array[5,6,7])
) as v(x,ya) 
  cross join unnest(v.ya) as t(y)

answered May 20, 2020 at 11:37

user330315

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

1 Answer 1