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?

Voluptuous

Answer 1

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

Enforce

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.. – Voluptuous

Answer 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.

Desiredesirea

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

True. It's one of the options. – Desiredesirea

Answer 3

Postgres version: 15.4 Example using gen_random_uuid():

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

Unsavory