I have a one-to-many relationship between two tables, where IDs are automatically generated on insertion. I want to insert a single row into the "parent" table, then use the generated ID of that row as a foreign key when I insert multiple rows into the "children" table.
For example, say I have this schema:
CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (), username TEXT NOT NULL, ); CREATE TABLE posts ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (), author_user_id UUID NOT NULL REFERENCES users (id), body TEXT, );
In postgres I can insert multiple rows using
INSERT INTO posts (author_user_id, body) VALUES (<some uuid here>, "Hello, World!"), (<some uuid here>, "I'm hungry.");
I can also insert a user and a single post referencing that author using a common table expression:
WITH john AS ( INSERT INTO users (username) VALUES (john_smith) RETURNING id ) INSERT INTO posts (author_user_id, body) SELECT john.id, "Hello, World!" FROM john;
Is there a way I can combine the two? i.e. insert a single user, get the ID of that new row, and use that ID when inserting multiple posts?
It doesn't have to be via a CTE if there's a simpler way. Thanks!