How can I reuse the same value from postgres' gen_random_uuid () for two one-to-one relationship tables

Question

I have a User table and a Profile table - they do have a 1-to-1 relationship. Given the Primary-Foreign Key.

When I add a new row to user table, ideally, I like to also add a trigger or make a transaction to insert some default values into profile too. However because of the PK-FK on Profile, I need to preserve the gen_random_uuid () as a variable to insert into both table. is there anyway of doing that without writing an external uuid generator in application?

enter image description here

• mustaccio: 1. You say one-to-one, but your diagram shows the one-to-many relationship. 2. If it's truly one-to-one, why do you think you need a separate table for the profile? 3. An after insert trigger has access to all inserted column values, so I'm not sure what problem you have with userId.
• Qi luo: Forgive me, i made a mistake there with the relationships. i meant to draw one to one. Thanks for the clarification in the answer! Yeah, thats a good question, I made that vertical separation because I think its a better representation of resources that would be consumed by the front-end side and easier for queries statements. I do wonder if that is wrong logic to make that separation? I read some articles about goods and bads, but still cant set in stone. I just can't imagine a user table pack with a lot of stuff, it would be a lot to unpacked and resorted on the api end no? thanks again.

Answer 1

You can do that with a data modifying common table expression

with new_user as (
 insert into "user" (userid, username)
 values (gen_random_uuid(), 'Qi luo')
 returning userid
)
insert into profile (userid, title)
select userid, 'Default Profile'
from new_user;