Click here to Skip to main content
Click here to Skip to main content
Go to top

Creating newid for PostgreSQL

, 4 Feb 2010
Rate this:
Please Sign up or sign in to vote.
Generating uuids in postgres without uuid ossp

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.

History

  • 2010-02-04 - Modified SQL for call, it didn't seem to work as expected with the quotes
  • 2010-02-04 - Original article

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Alaric Dailey
Software Developer (Senior) Pengdows
United States United States
Currently looking for new contracts in Omaha NE or telecommute opportunities.

Comments and Discussions

 
GeneralGood, but... PinmemberWin32nipuh10-Mar-10 5:54 
GeneralRe: Good, but... PinmemberAlaric Dailey10-Mar-10 6:01 
GeneralMy vote of 2 PinmemberMember 33387468-Feb-10 20:14 
GeneralRe: My vote of 2 PinmemberAlaric Dailey9-Feb-10 3:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140905.1 | Last Updated 4 Feb 2010
Article Copyright 2010 by Alaric Dailey
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid