Shurik Agulyansky Shurik Agulyansky - 1 year ago 48
SQL Question

How to build relationships using data from two tables?

I am building a migration in Postgres where I need to add one new user for every organization.

Assuming that I have tree tables:

  • user

  • org

  • user_org

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
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?

Answer Source

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
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download