Creating newid for PostgreSQL

Introduction

Without installing the uuid-ossp, PostgreSQL doesn't support an equivalent to the Microsoft SQL Server function newid(), so I set out to create one.

Background

I started out by reading the uuid RFC, the Wikipedia article on uuids and the documentation on newid(). It turns out that it generates a version 4 uuid. Those are based on random or pseudo-random numbers, with certain nibbles set to certain values. To explain what that means, in simple terms if we replace all the random numbers with 'x', you will see that at position 14 there is the number '4', and at position 19, is a 'y'.

xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx

The number '4' at position 14 is obviously for the version number, at that position you will be able to always find the version number. The 'Y' at position 19 you are supposed to set the bits specifically, if you can stand reading the RFC, you will end up with 4 possible values '8', '9', 'A' or 'B'.

All of this is well and good, but from inside SQL we don't have bit manipulation functions, but we do have string manipulation functions.

To complicate matters, the random function only generates a "random value in the range 0.0 <= x < 1.0". Furthermore, it only returns a double.

So I started looking at other versions. Version 3 takes advantage of something I already knew, an MD5 signature is the same length as a uuid. That version uses namespaces to create a unique blob of data, and then creates an MD5 hash of that data, then replaces position 14, with a '3'.

The problem with using version 3 uuids is if we stick to the specification, it is supposed to be able to generate the same uuid given the same inputs. This isn't exactly what I want.

So my thought was to borrow a little from both, to create a unique bit of data, that should create a random hash. To start, I will grab the current database name, the current user name, the current timestamp, and a call to random(). Finally I will create an MD5 hash of that data.

CREATE OR REPLACE FUNCTION dbo.newid()
  RETURNS uuid AS
$BODY$
 SELECT CAST(md5(current_database()|| user ||current_timestamp ||random()) as uuid)
$BODY$
  LANGUAGE 'sql' VOLATILE

I skipped replacing the values correctly, because of the lack of fast manipulation functions. If at some point you get the uuid-ossp module installed, then you can change the function to the following:

CREATE OR REPLACE FUNCTION dbo.newid()
  RETURNS uuid AS
$BODY$
 SELECT uuid_generate_v4()
$BODY$
  LANGUAGE 'sql' VOLATILE

Using the Code

Obviously you could simply do a select:

SELECT dbo.newid()

However the reason for wanting this function in the first place is to prepopulate columns in tables, and so you can create tables like this:

CREATE TABLE dbo.sometable
(
  id uuid NOT NULL DEFAULT dbo.newid(),
  created_by character varying(50) NOT NULL,
  created_on timestamp with time zone NOT NULL DEFAULT now(),
  modified_by character varying(50) NOT NULL,
  modified_on timestamp with time zone NOT NULL DEFAULT now()
)
WITH (
  OIDS=FALSE
);

Points of Interest

This function obviously isn't creating uuids of any strict version number, but it does create uuids that have a high probability of being unique, allowing an easier move from SQL Server to PostgreSQL.

I created the function in the dbo schema rather than in the default "public" schema, because that is where it exists in SQL Server.