How to build relationships using data from two tables?

Question

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

Not sure I'm completely understanding your question. Assuming you have 10 orgs, you want to insert 10 users, 1 at each org? Perhaps sample data and expected results would be helpful. — sgeddes, Aug 03 '16 at 23:16

sgeddes · Accepted Answer · 2016-08-03T23:29:56.607

2

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

SQL Fiddle Demo

edited Aug 03 '16 at 23:29

answered Aug 03 '16 at 23:26

sgeddes

62,311
6
61
83

Looks like it does what I need, I'll do some more testing and post back. Thanks! – Shurik Agulyansky Aug 03 '16 at 23:31
`order by id` in both subqueries would require that the *values* of `usr.id` and `org.id` are in sync - which is a very unlikely assumption. Certainly not based on information in the question. Typically, this query wouldn't work. – Erwin Brandstetter Aug 04 '16 at 03:38
@ErwinBrandstetter -- Agreed -- my original question prior to posting asked similarly. I assumed it was arbitrary given the reply, so this implementation with `row_number` should work. However, preferably there would be a correlation between users and organizations. – sgeddes Aug 04 '16 at 03:49
@ErwinBrandstetter -- After reading your post (in a query that actually works), you presume a user org is created when a user is. If that isn't the case, as I didn't assume, your query wouldn't work with the `join`... The point as I understood was to take 2 tables with x number of records in each (the same), and insert a 1-1 in a joining table (don't know why). In addition to my question to the OP, this stood out to me `(assuming that number of created users and numbers of orgs matching)` Regardless, irritable response you had imo... – sgeddes Aug 04 '16 at 04:10
There is no importance of matching specific user to specific org, the only important this is that every org with one one of these users. So the solution works fine. Thanks – Shurik Agulyansky Aug 04 '16 at 05:39
@ShurikAgulyansky: It seemed to me that `users that I am copying from already associated with orgs` implies that users already associated with orgs (with an entry in `usr_org`) serve as template and each new user would need to be associated with the same org respectively. I must have misread that, sorry. – Erwin Brandstetter Aug 04 '16 at 11:31

score 0 · Answer 2 · edited May 23 '17 at 12:32

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 there is any subset of columns to identify unique user without PK, there is a secure solution with data-modifying CTEs. You could even insert multiple user-copies at once:

WITH template_user AS (
   SELECT u.*, uo.org_id  -- include the connected org_id
   FROM   users    u
   JOIN   user_org uo ON uo.user_id = u.id  -- guessing column names
   WHERE  ...  --  make sure exactly *one* desired org is joined
   )
, ins_user AS (
   INSERT INTO users u (...)
   SELECT ... 
   FROM   template_user t
   WHERE  ...
   RETURNING *
   )
INSERT INTO user_org (user_id, org_id)
SELECT iu.id, tu.org_id
FROM   ins_user      iu
JOIN   template_user tu USING (<list of columns that secures distinct join>)
RETURNING *;  -- optional

In the unlikely case that you shouldn't have a reliable set of columns to join to the template users: add a row number, like @sgeddes already suggested, but in a query that actually works:

WITH template_usr AS (
   SELECT u.*, uo.org_id  -- include the connected org_id
          row_number() OVER () AS rn  -- arbitrary number
   FROM   usr     u
   JOIN   usr_org uo ON uo.usr_id = u.id  -- guessing column names
   WHERE  ...  --  make sure exactly *one* desired org is joined
   )
, ins_usr AS (
   INSERT INTO usr u (...)  -- add target columns
   SELECT ... 
   FROM   template_usr
   ORDER  BY rn             -- insert in same order!
   RETURNING id
   )
INSERT INTO usr_org (usr_id, org_id)
SELECT iu.id, tu.org_id
FROM  (
   SELECT id, row_number() OVER (ORDER BY id) AS rn
   FROM   ins_usr
   ) iu
JOIN   template_usr tu USING (rn)
RETURNING *;  -- optional

How to build relationships using data from two tables?

2 Answers2