Re: Generating random unique alphanumeric IDs

On Sun, Aug 16, 2009 at 12:07:27PM +0100, Thom Brown wrote:
> Does anyone know a way to generate a random and unique lowercase
> alphanumeric ID

If you want it to be unique then it's not going to be random. The
easiest way to keep it from producing duplicates is to have some
monotonically increasing component. If you're OK with code/people
retrying the occasional duplicate then you're going to be relying on
statistical guarantees and you should look at "birthday attacks" to see
how often this is going to happen.

> Notice that I don't mean hexadecimal values either. This would preferrably
> not resort to trying to generate the ID, then checking for a clash, and if
> there is one, do it again, although that could do as I can't think of how
> the ideal solution of a ID hashing algorithm would be possible.

The following is the obvious PGSQL code, you'd obviously need something
else to stop duplicates.

SELECT array_to_string(array((
SELECT SUBSTRING('abcdefghjklmnpqrstuvwxyz23456789'
FROM mod((random()*32)::int, 32)+1 FOR 1)
FROM generate_series(1,5))),'');

As this only generates five characters and each character can be one of
32 values, you've got about 33554432 choices and you'd have a 50% chance
of getting a duplicate after 7240 values. This assumes I wrote the
above code correctly. It's also not amazing because PG's random number
generator is defined to return a value between 0 and 1 inclusive, it's
generally much more useful if it runs from 0 to less than 1 and would
mean that I wouldn't need the "mod" above and would remove the (slight)
biasing towards choosing 'a'.

--
Sam http://samason.me.uk/