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
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?
Advertisement
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;