Variables in PostgreSQL with UUID

Question

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?

@wingᴇdpᴀnᴛʜᴇʀ, Can you provide an example please? — Sogl
– Sogl, Commented Oct 6, 2015 at 4:59

Evdzhan Mustafa · Accepted Answer · 2019-04-04 13:18:20Z

1

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

answered Apr 4, 2019 at 13:18

Evdzhan Mustafa

3,7351 gold badge29 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tbenst Over a year ago

When I try this, I get FatalError: invalid input syntax for type uuid: "my_uuid"

Patrick · Accepted Answer · 2015-10-06 06:01:48Z

0

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.

edited Oct 6, 2015 at 6:01

answered Oct 6, 2015 at 5:56

Patrick

33k7 gold badges73 silver badges102 bronze badges

3 Comments

Sogl Over a year ago

I use the same query as in your 2nd example. Problem starts when you already have many users with same firstname and lastname with many posts in each user and try to add FK from one specific user's post to another table (tags, for example). What do you think? Variables would be the best solution here.

Patrick Over a year ago

Assuming that this is some web-based system, you should store the uuid of the app user in the session context. Then your queries should look up the uuid from the session information and use that when inserting a post.

Sogl Over a year ago

You right. But there is no clientside in this situation, just test DB filling. I found a workaround how to select 3rd post id of specific user (for FK): select "id" from "Post" WHERE "userId" = (SELECT id FROM public."User" WHERE name='Foo Bar') LIMIT 1 OFFSET 2

vahem2lu · Accepted Answer · 2020-06-18 10:01:14Z

0

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

answered Jun 18, 2020 at 10:01

vahem2lu

262 bronze badges

Collectives™ on Stack Overflow

Variables in PostgreSQL with UUID

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest