CODEWITHSUNDEEP
postgresqltextcastingsql-updateuuid
knightrider
knightrider

Reputation: 237

How to insert value into uuid column in Postgres?

I have a table with a uuid column, and some of the rows are missing the data. I need to insert data into this uuid column. The data is entered manually, so we are suffixing with other column data to differentiate, but it gives me an error.

UPDATE schema.table 
    SET uuid_column = CONCAT ('f7949f56-8840-5afa-8c6d-3b0f6e7f93e9', '-', id_column) 
WHERE id_column = '1234';

Error: [42804] ERROR: column "uuid_column" is of type uuid but expression is of type text
Hint: You will need to rewrite or cast the expression.
Position: 45

I also tried 

UPDATE schema.table 
   SET uuid_column = CONCAT ('f7949f56-8840-5afa-8c6d-3b0f6e7f93e9', '-', id_column)::uuid 
WHERE id_column = '1234';

Error: [22P02] ERROR: invalid input syntax for uuid: "f7949f56-8840-5afa-8c6d-3b0f6e7f93e9-1234"

Upvotes: 4

Views: 7790

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246513

An UUID consists of 16 bytes, which you see displayed in hexadecimal notation.

You cannot have a UUID with fewer or more bytes.

I recommend using the type bytea if you really need to do such a thing.

Upvotes: 2

Related Questions