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