I've got table x with many existing rows
x ( id, name)
I've got a new table y, which is currently empty, (all fields have default values)
y ( id, uuid )
I've updated x with a new column y_id
x ( id, name, y_id )
I want to populate y for every row in x, and then associate x with y via y.id
This is as close as I've got but this sets all rows of x to have the same y.id.
with ys as (
insert into y(uuid) values(default)
returning id
)
update x set y_id = ys.id
from ys
You want to populate "y" for every value of "x". But then you don't have a way to connect the tables. But . . .
with ys as (
insert into y
select -- this is empty on purpose to put in only default values
from x;
returning id
)
update x
set y_id = yy.id
from (select x.*, row_number() over (order by x_id) as seqnum
from x
) xx join
(select ys.*, row_number() over (order by y_id) as seqnum
from ys
) yy
on xx.seqnum = yy.seqnum
where x.x_id = xx.x_id;
What does this do? The CTE inserts a row into y with default values for every row in x. The insert then adds a sequence number to the x's and y's so they can be aligned, one row to one row. That value is then used for the update.