Case insensitive UUID


(york) #1

I have a table with UUID as primary key. When inserting an entry with all lowercase UUID, it works fine, e.g.
insert into “my_table” (id) values (‘1eb41c3c-6100-44fc-abcb-67fcfc378191a’);

But when inserting with upper case letters in the UUID, it is converted into lower case automatically. e.g.
insert into “my_table” (id) values (‘1eb41c3c-6100-44fc-abcb-67fcfc378191A’);
it will cause a duplicated key error.

But the UUID with upper cases are still valid uuid. My question is: is there a flag to set in the cockroachdb, so that UUID can be case sensitive?


(Tim O'Brien) #2

Hi @ggyao2003,

The UUID spec dictates that UUIDs should be output as lower case, and case insensitive on input. We don’t have any plans to adjust our implementation of UUID to be case sensitive, but if you want to you can create a github issue outlining your use case and the need for such a feature.

Hope that helps,

Tim


(Raphael 'kena' Poss) #3

To add to Tim’s answer.
The letters you use in the SQL strings are only the human representation.
Inside the database (like in every other software using UUID!) the string is converted to a 128-bit value. All the letters are treated as a single hexadecimal digit (0-9, a/A b/B c/C d/D e/E f/F) and translated to 4 bits. There are 32 characters to form the 128 bits of the values.

In this translation there can be only 16 different digits/letters in each position. 0-9 give 10 of them. Then there is only “space” for 6 more characters. We cannot “fit” more than the a,b,c,d,e,f lettes; that is why the capital letters A,B,C,D,E,F are treated as equivalent.

In other words “case sensitive UUID” is a sentence that makes no sense once you understand that UUID values are simply just 128 bit integers.