Insert a record into database and associate it to a record in another table without the id

This may be a duplicate question, but I do not know the terminology to use to search for the answer.

I have an account table with the following fields:

• id
• email

And a widget table with the following field:

• id
• name
• account_id

The account_id is set as foreign key and it references the account(id).

How do I insert a widget and associate it to an existing account record in a single query if only the account email is known?

Answer

Assuming account.id is an identity (or serial) column, you can use a writetable CTE:

with new_account as (
  insert into account (email) values ('....')
  returning id
)
insert into widget (name, account_id)
select id, 'new widget'
from new_account;