PostgreSQL update statement referencing newly inserted IDs

2/24/2025 (updated: 1/1/2026)

Situation

I have two child tables that each have a foreign key to a parent table in PostgreSQL. Child_b also has a foreign key to child_a. Child_a previously was optional but is now required, at least one per parent. I want to insert rows into child_a, and then update existing rows in child_b’s foreign key to the newly inserted child_a ids.

parent_id

parent_id

child_a_id

parent

child_a

child_b

Solution

WITH child_b_missing_child_a AS 
     SELECT
         b.id AS b_id,
         a.parent_id
     FROM child_b b
     LEFT JOIN child_a a ON b.child_a_id = a.id
     WHERE a.id IS NULL
 ),
 child_a_inserts AS (
     INSERT INTO child_a
         (name, status, updated_at, parent_id)
     SELECT
         'placeholder', 'pending', now(), parent_id
     FROM (select distinct parent_id from child_b_missing_child_a) as parents
     RETURNING id, parent_id
 )
 UPDATE child_b b
 SET child_a_id = m.child_a_id, updated_at = now(), update_authenticated_by = 0, updated_by = 0
 FROM (select a.id as a_id, b_id
       from child_a_inserts a
       join child_b_missing_child_a missing on a.parent_id = missing.parent_id) as m
 WHERE b.id = m.b_id and b.child_a_id is null;