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.
BackgroundI 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'.
Collapsexxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxxThe 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.
Read more: Codeproject
BackgroundI 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'.
Collapsexxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxxThe 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.
Read more: Codeproject