Syntax to manually insert a UUID value in Postgres

Question

I have a table that was created as such:

CREATE TABLE IF NOT EXISTS DIM_Jour (
    jour_id uuid NOT NULL,
    AAAA int,
    MM int,
    JJ int,
    Jour_Semaine int,
    Num_Semaine int,

    PRIMARY KEY (jour_id)
);

I'm trying to manually insert some value for testing purposes. I know that eventually I would need to use a UUID generator.

INSERT INTO DIM_Jour (jour_id, AAAA, MM, JJ, Jour_Semaine, Num_Semaine) VALUES (
    292a485f-a56a-4938-8f1a-bbbbbbbbbbb1,
    2020,
    11,
    19,
    4,
    47
);

I get this error (or similar)

ERROR:  syntax error at or near "a485f"
 LINE 3:  292a485f-a56a-4938-8f1a-bbbbbbbbbbb1,
             ^

I've tried the different formats mentioned in the Postgres documentation, but it seems like it doesn't except any format. Is it a stupid syntax issue or am I missing something here? What is the correct syntax?

Mureinik · Accepted Answer · 2020-11-19 15:40:03Z

14

You could pass it as a string literal and have the database implicitly convert it to a UUID:

INSERT INTO DIM_Jour (jour_id, AAAA, MM, JJ, Jour_Semaine, Num_Semaine) VALUES (
    '292a485f-a56a-4938-8f1a-bbbbbbbbbbb1',
    2020,
    11,
    19,
    4,
    47
);

But it's probably a good practice to be explicit about it and perform the cast yourself

INSERT INTO DIM_Jour (jour_id, AAAA, MM, JJ, Jour_Semaine, Num_Semaine) VALUES (
    '292a485f-a56a-4938-8f1a-bbbbbbbbbbb1'::UUID,
    2020,
    11,
    19,
    4,
    47
);

edited Nov 19, 2020 at 15:40

user330315

answered Nov 19, 2020 at 15:36

Mureinik

316k54 gold badges399 silver badges405 bronze badges

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

1 Comment

Jonath P Over a year ago

I thought I had tried with single quotes around it, but there must have been another error the time I imagine. Thanks for the confirmation. It works..

Community · Accepted Answer · 2021-10-07 11:32:16Z

2

You should use UUID generator and install UUID extension. PostgreSQL requires valid UUIDs conforming to RFC 4122. Random strings are not valid UUIDs.

Also, as it is mentioned below, you should pass your UUIDs as quoted strings.

edited Oct 7, 2021 at 11:32

CommunityBot

11 silver badge

answered Nov 19, 2020 at 15:38

adamkonrad

7,1222 gold badges38 silver badges42 bronze badges

3 Comments

user330315 Over a year ago

Note that starting with Postgres 13, the extension is no longer needed to generate UUID. Postgres how has gen_random_uuid() built-in

adamkonrad Over a year ago

True. It's one of the options.

tekHedd Mar 12 at 20:51

Pro tip: this approach doesn't work very well if you're trying to insert a specific, non-random UUID, say, if you were doing it for testing purposes.

Manohar Rana · Accepted Answer · 2024-04-02 15:09:55Z

-1

Postgres version: 15.4 Example using gen_random_uuid():

INSERT INTO "public"."tablename" ("id", "uuid","name") VALUES (708, gen_random_uuid(),'some name');

answered Apr 2, 2024 at 15:09

Manohar Rana

1391 silver badge5 bronze badges

Collectives™ on Stack Overflow

Syntax to manually insert a UUID value in Postgres

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

Related