Variables in PostgreSQL with UUID

All my tables use UUID primary keys. I want to fill tables with test data in one sql file, but can't use UUID PK as FK in other tables, because default function uuid_generate_v4() generates a random value during execution.

Example what I want:

SET Uservar = uuid_generate_v4(); SET Postvar = uuid_generate_v4(); INSERT INTO public."User" (id, name) VALUES (Uservar, 'Foo Bar'); INSERT INTO public."Post" (id, content, userId) VALUES (Postvar, 'Test message', Uservar) 

How to do this? Or how to select already created UUID and store for next insert?

3 Answers

E.g. say you had table like this:

create table my_table(uuid_column uuid PRIMARY KEY NOT NULL); 

You can insert a variablized uuid like so:

DO $$ DECLARE my_uuid uuid = uuid_generate_v4(); BEGIN insert into my_table (uuid_column) values (my_uuid); select * from my_table where uuid_column = my_uuid; END $$; 

Check this documentation.

N.B. To have uuid_generate_v4() available, make sure you have the below snipped ran before your use it:

 CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; 

When you combine both INSERTs into a single statement you can re-use the uuid values quite easily:

WITH newUser(id uuid) AS ( INSERT INTO public."User" (id, name) VALUES (uuid_generate_v4(), 'Foo Bar') RETURNING id ) INSERT INTO public."Post" (id, content, userId) SELECT uuid_generate_v4(), 'Test message', id FROM newUser; 

When you want to add a post for an existing user, you can use a very similar approach:

INSERT INTO public."Post" (id, content, userId) SELECT uuid_generate_v4(), 'Test message', id FROM public."User" WHERE name = 'Foo Bar'; 

This would also work when the PK's are auto-generated (i.e. id uuid PRIMARY KEY DEFAULT uuid_generate_v4()) but then you would not explicitly include the PK columns in the INSERT statements.

As I cannot comment

it should be

DO $$ DECLARE my_uuid uuid := uuid_generate_v4(); BEGIN insert into my_table (uuid_column) values (my_uuid); select * from my_table where uuid_column = my_uuid; END $$;