4

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"

9
  • 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
    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
    Commented Feb 13, 2019 at 18:23
  • 3
    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
    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
    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
    Commented Feb 13, 2019 at 18:48

1 Answer 1

Reset to default
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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.