I am building a migration in Postgres where I need to add one new user for every organization.
Assuming that I have tree tables:
and list of organizations are already exist in the DB.
Now I am creating new users based on other users and modified data like so:
insert into users
select
...
from users where ...
returning id
What I need now is to associate every user to one organization (assuming that number of created users and numbers of orgs matching).
cross join is not going to work as it will associate all users to all orgs.
The list of users that I am copying from already associated with orgs, is there an easy way to build my query based on that, given that newly created users have different keys?
If I'm understanding correctly, here's one option using an inner join, and then establishing a row_number per table to join on:
insert into user_org
select u.id, o.id
from (select id, row_number() over (order by id) rn
from usr) u join
(select id, row_number() over (order by id) rn
from org) o on u.rn = o.rn