How to insert value into uuid column in Postgres?

Question

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"

Well, you can't do what you are trying to do there. f7949f56-8840-5afa-8c6d-3b0f6e7f93e9 is already a complete and valid UUID, you can't append anything to that value. What exactly is the problem you are trying to solve with that? If you want to generate a UUID, install the uuid-ossp extension and use e.g. uuid_generate_v4() — user330315
– user330315, Commented Feb 13, 2019 at 18:21
I am trying to update missing uuid data. since we are adding it manually, we need something to differentiate between manually added and systematically added and that's why we are suffixing with id_column. — knightrider
– knightrider, Commented Feb 13, 2019 at 18:23
For that add something like a boolean column telling uuid_column is manually added. Another option is to change the column type uuid to varchar but that makes things just difficult elsewhere. — pirho
– pirho, Commented Feb 13, 2019 at 18:31
okay, so it looks like we cannot suffix or prefix anything while inserting into uuid column. Is that a correct statement? I do not like the option to convert into varchar, that will create a mess. — knightrider
– knightrider, Commented Feb 13, 2019 at 18:32
Correct, you cannot. As you cannot insert - say - text to bigint column. It is not anymore uuid as rfc4122 specifies it. — pirho
– pirho, Commented Feb 13, 2019 at 18:48

Laurenz Albe · Accepted Answer · 2019-02-14 08:50:46Z

2

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.

answered Feb 14, 2019 at 8:50

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

How to insert value into uuid column in Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related