Create one row for every user in a Postgres table I have a bunch of rows of users in a users table, for example:

| id |  name |
|----|-------|
|  1 | Chris |
|  2 | Max   |
|  3 | Steve |


For each one of these users, I'd like to create a row in another table called user_flags:

| user_id |  flag  |
|---------|--------|
|       1 | myflag |
|       2 | myflag |
|       3 | myflag |


Currently what I'm doing is downloading users in batches of ~100 at a time, and then doing a bulk INSERT query for each batch. However I think that there should be a quicker way.

The goals:


Insert the flag rows as quickly as possible
Long running queries are fine, as long as there's no table locking anywhere
These are real-time queries, so indexes cannot be dropped
Should be able to comfortably run over millions of user rows


Is this possible? I feel like with some smart functions it could/should be.
This would seem rather simple:

INSERT INTO user_flags (user_id, flag)
SELECT u.id, 'myflag'   -- or whatever to write to this field
FROM   users u
LEFT   JOIN user_flags uf ON uf.user_id = u.id
WHERE  uf.user_id IS NULL;  -- to exclude already existing


How to ..


Select rows which are not present in other table


While this is susceptible to race conditions with concurrent transactions, that should not be a problem since you are not going to execute this INSERT in more than one transaction at a time.

You might limit the operations to n rows per call, say 100000, simply append LIMIT, possibly combined with ORDER BY:

...
ORDER  BY u.id
LIMIT  100000;


Repeat the query until you find &lt; 100000 rows, in separate transactions to release locks. Normally, you would do it all in one command, though, even with millions of rows.

I suggest a FOREIGN KEY constraint to enforce referential integrity - if you don't have one, yet.

ALTER TABLE user_flags
  ADD CONSTRAINT user_flags_user_id_fkey FOREIGN KEY (user_id)
  REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;


And a UNIQUE or PRIMARY KEY constraint on user_flags.user_id to make the 1:1 relationship complete.