Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE b (id uuid PRIMARY KEY);
CREATE TABLE a (id int, b_id uuid REFERENCES b);

INSERT INTO b(id) VALUES
  ('acb572f6-0b89-41dd-84a3-6ac4c68cbc47')
;

INSERT INTO a VALUES
  (1, 'acb572f6-0b89-41dd-84a3-6ac4c68cbc47')
, (2, NULL)
, (3, NULL)
, (4, NULL)
;
1 rows affected

4 rows affected

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;
3 rows affected

TABLE a;
TABLE b;


id
b_id


1
acb572f6-0b89-41dd-84a3-6ac4c68cbc47

2
f5a7609f-957f-468d-b5d7-e928b76ceabd

3
c3d71176-679f-414c-9699-6972b49c3d6a

4
01c4016a-3e40-4807-bbd1-5e21a6232815




id


acb572f6-0b89-41dd-84a3-6ac4c68cbc47

f5a7609f-957f-468d-b5d7-e928b76ceabd

c3d71176-679f-414c-9699-6972b49c3d6a

01c4016a-3e40-4807-bbd1-5e21a6232815

id	b_id
1	acb572f6-0b89-41dd-84a3-6ac4c68cbc47
2	f5a7609f-957f-468d-b5d7-e928b76ceabd
3	c3d71176-679f-414c-9699-6972b49c3d6a
4	01c4016a-3e40-4807-bbd1-5e21a6232815