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

by

The 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?

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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;


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 Comment