PostgreSQL UUID Extension

Stefan Arentz, 2007-07-28

This module implements a UUID extension for PostgreSQL 8.x. It has been tested on PostgreSQL 8.2.x on OS X 10.4 and Ubuntu 6.06/LTS.

Advantages

Database Size - Using a native UUID type on a table that also has an index on the uuid column saves 47% space. This is good because there is less data on disk and in memory. Which really matters if you have potentially hundred of millions of records. The native UUID type uses 16 bytes of storage whereas a ascii uuid column uses 38 bytes.

Consistency - The native UUID type only accepts UUIDs that are well formatted. Of course you can also do this with a constraint check, but having a native type with a fixed format is more elegant in my opinion.

Transparent - Nobody is going to notice the difference. Because of the implicit typecasting you can use UUIDs as if they are ascii values.

Installing

Installation is done like this:

% svn co http://polarrose-postgresql-uuid.googlecode.com/svn/trunk polarrose-postgresql-uuid % cd polarrose-postgresql-uuid % sudo make install

Activation like this:

% createdb mydatabase % psql mydatabase -f uuid.sql

Using

You can then use the 'uuid' type like this:

create table foo ( id uuid not null );

And generate uuids through the uuid() function.

insert into foo (id) values (uuid());

Cast operators have been provided so that the uuid type can be parsed to and from it's textual representation:

insert into foo (id) values ('36db183f-b1a1-4237-aea4-bc95b5a3bfd8');

And the other way around:

select id::text from foo;

Enjoy.