PostgreSQL 9.3: How to insert upper case UUID into

问题:

I have the following table with only 1 column that is id which is of type UUID.

Table: uuidtest

create table uuidtest
(
id uuid
);

Insertion:

I have generated uuid by using uuid_generate_v4() and also done it upper case and inserting it into the table.

Try1:

insert into uuidtest values('{4B36AFC8-5205-49C1-AF16-4DC6F96DB982}');

Try2:

insert into uuidtest values('4B36AFC8-5205-49C1-AF16-4DC6F96DB982');

Now see the result:

select * from uuidtest;

Ouptput:

id
uuid
-------------------------------------
4b36afc8-5205-49c1-af16-4dc6f96db982
4b36afc8-5205-49c1-af16-4dc6f96db982

I have also tried it to update:

update uuidtest
set id = upper(id::text)::uuid

But: The output remain the same:

id
uuid
-------------------------------------
4b36afc8-5205-49c1-af16-4dc6f96db982
4b36afc8-5205-49c1-af16-4dc6f96db982    

回答1:

First of all, you should note that in PostgreSQL a UUID is a 128 bit number, and is stored as such (not as a string with 36 characters!). What we are discussing are the input and output forms of this type of data.

The PostgreSQL manual says:

The data type uuid stores Universally Unique Identifiers (UUID) as defined by RFC 4122, ISO/IEC 9834-8:2005, and related standards.

...

PostgreSQL also accepts the following alternative forms for input: use of upper-case digits, the standard format surrounded by braces, omitting some or all hyphens, adding a hyphen after any group of four digits.

...

Output is always in the standard form.

The RFC 4122 says:

Each field is treated as an integer and has its value printed as a zero-filled hexadecimal digit string with the most significant digit first. The hexadecimal values "a" through "f" are output as lower case characters and are case insensitive on input.

In other words, to follow the standard, the value is always printed as lower case.

Of course, if you want to produce the result with upper case letters, you could use something like:

select upper(id::TEXT) from uuidtest;

回答2:

Yeah thats right. If inserted in Postgres, it will force the values to go to lowercase. What I tried was bit different and may not be usable for everyone i.e; may not be practical. If you want UUIDs in upper case in Postgres then use ID as this one:

    String id = java.util.UUID.randomUUID().toString().toUpperCase();

Snipped picture to show IDs in CAPS

Also, if this is not useful you have to try as others mentioned above: DB generated UUID in lower:

select uuid_generate_v4();

in UPPER:

select UPPER(uuid_generate_v4()::text);