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

All we need is an easy explanation of the problem, so here it is.

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?

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply