CODEWITHSUNDEEP
postgresqlsyntaxsyntax-erroruuid
Jonath P
Jonath P

Reputation: 561

Syntax to manually insert a UUID value in Postgres

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?

Upvotes: 8

Views: 13556

Answers (3)

Manohar Rana
Manohar Rana

Reputation: 129

Postgres version: 15.4 Example using gen_random_uuid():

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

Upvotes: -1

Mureinik
Mureinik

Reputation: 311073

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

Upvotes: 12

adamkonrad
adamkonrad

Reputation: 7122

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.

Upvotes: 2

Related Questions