Convert PostgreSQL in bytes with serial-java-UUID to postgresql-UUID

Question

One of our software projects uses a PostgreSQL table with a "guid" column of type bytea.

This is used with hibernate 3.3.2.GA with PostgreSQL 8.4, which serializes the Java UUID type using serialization of Java objects . The result is a value similar to the following escape format bytea literal:

 '\254\355\000\005sr\000\016java.util.UUID\274\231\003\367\230m\205/\002\000\002‌​J\000\014leastSigBitsJ\000\013mostSigBitsxp\273\222)\360*r\322\262u\274\310\020\3‌​42\004M ' 

... which we cannot easily use in the query as a choice or condition for obtaining the corresponding rows.

Does anyone have a way to read or use a byte column in a selection section or somewhere in a request (e.g. via psql or pgadmin3) without setting up any sleeping request?

Answer 1

After some trial and error, I created the following function to retrieve the value of postgresql-UUID:

CREATE OR REPLACE FUNCTION bytea2uuid (x bytea) RETURNS uuid as $$ SELECT encode(substring(x, 73, 8) || substring(x, 65, 8), 'hex')::uuid $$ language sql;

This works by extracting the bytes used in long java values ​​for the smallest values ​​of SigBits and mostSigBits (which are stored in reverse order) than encoding in hex and casting for input of type uuid.

Used as follows: select bytea2uuid(guid) as guid from documents limit 1;

"75bcc810-e204-4d20-bb92-29f02a72d2b2"

Answer 2

Refresh . See the Editing a Question section. This answer relates to the usual 16-byte uuid serialization; The question has been modified to reflect Java serialization.

---

An interesting problem. I landed by writing a simple C extension to do this efficiently, but it is probably more prudent to use the PL / Python version below.

Since uuid is a type with a fixed size, and bytea is a varlena , you can simply create cast ... as implicit use them binary-forcefully, because the header of a variable-length field will interfere.

There is no built-in function for entering bytea to return uuid. That would be convenient, but I don't think anyone else did it.

Simplest way

Refresh . This is actually an easy way to do this. bytea in hexadecimal is actually a valid uuid literal after removing \x , because uuid_in accepts a simple, non-decode hex without - or {} . So simple:

 regress=> SET bytea_output = 'hex'; SET regress=> SELECT CAST( substring(CAST (BYTEA '\x0FCC6350118D11E4A5597DE5338EB025' AS text) from 3) AS uuid); substring -------------------------------------- 0fcc6350-118d-11e4-a559-7de5338eb025 (1 row) 

It includes a couple of string copies and a hexadecimal encoding / decoding cycle, but it will be several tons faster than any of the PL answers I suggested earlier, albeit slower than C.

Other options

Personally, I recommend using PL / Perl or pl / pythonu. I will give an example.

Assuming your uuid is a hex byte literal:

 '\x0FCC6350118D11E4A5597DE5338EB025' 

you can turn it into uuid type with:

PL / Perl

 create language plperlu; create or replace function to_uuid(bytea) returns uuid language plperlu immutable as $$ use Data::UUID; my $ug = new Data::UUID; my $uuid = $ug->from_hexstring(substr($_[0],2)); return $ug->to_string($uuid); $$ SET bytea_output = hex; SELECT to_uuid(BYTEA '\x0FCC6350118D11E4A5597DE5338EB025'); 

PL / Python

It is probably faster and cleaner in Python, because the PL / Python interface passes bytea as raw bytes, not as hexadecimal strings:

 CREATE LANGUAGE plpythonu; CREATE or replace function to_uuid(uuidbytes bytea) RETURNS uuid LANGUAGE plpythonu IMMUTABLE AS $$ import uuid return uuid.UUID(bytes=uuidbytes) $$; SELECT to_uuid(BYTEA '\x0FCC6350118D11E4A5597DE5338EB025'); 

In C, just for punches. Ugly hacking.

Here you can see the C extension module here .

But actually, I mean that it was ugly. If you want this to be done correctly in C, it's best to attack PostgreSQL and not use the extension.

Answer 3

This works for me:

 ALTER TABLE myTable ALTER COLUMN id TYPE uuid USING CAST(ENCODE(id, 'hex') AS uuid);