How to generate unique hash-type id?

From: Joe Kramer <cckramer(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to generate unique hash-type id?
Date: 2010-01-29 08:20:33
Message-ID: b4c00a111001290020q1a9acfd8l5e699c890d0b7f45@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I need to generate unique id which is not guessable unlike
serial(integer) type. I need an id in format like md5 hash of random
number.
On top of that I need this id to be unique across multiple tables.

Anyone had to solve this problem before? Can you post any recipes or
best practices please?

My questions:

1. Avoiding collisions.
If I make an UNIQUE constraint and do generation of id triggered on
INSERT. What if collision happens? DO I nee d to check if unique hash
already exists and if not- regenerate.
This looks too primitive. Is there a readily available function or
methodology to do that?

2. Generating global unique id across multiple tables.
How to do that? My only idea is to have separate table to keep all
hashes and compare for collision against that table.
Is there a better way? Maybe by creating some special serial type that
is not integer but varchar?

3. what function to use to generate 64-bit random hash without much
overhead to CPU?

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vincenzo Romano 2010-01-29 09:30:58 R: How to generate unique hash-type id?
Previous Message Rodger Donaldson 2010-01-29 08:07:21 Re: Amazon EC2 CPU Utilization