How to insert multiple rows into table B, and update table A's null foreign keys with the new IDs?

Question

I've found a million things sounding kind of similar on StackOverflow, but not my case exactly. I'll simplify as much as possible:

I have two tables as follows:

CREATE TABLE B (id uuid PRIMARY KEY);
CREATE TABLE A (id uuid PRIMARY KEY, b_id uuid REFERENCES b);

There are some NULL values in A.b_id. I am trying to create a migration that does the following:

For every row in A with no b_id, create a new row in B, and assign its id to A.b_id.

How can I accomplish this in one query?

user_939860 · Answer

Assuming you want a distinct entry in b for every row with a missing UUID in a:

WITH upd AS (
   UPDATE a
   SET    b_id = gen_random_uuid()
   WHERE  b_id IS NULL
   RETURNING b_id
   )
INSERT INTO b (id)
SELECT b_id FROM upd;

db<>fiddle here

This works because it's a single command, and the FK reference is only enforced at the end of the command.

See: