Skip site navigation (1) Skip section navigation (2)

Creating a unique identifier...

From: Chris Gamache <cgg007(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Creating a unique identifier...
Date: 2002-10-31 18:14:54
Message-ID: 20021031181454.40280.qmail@web13802.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
I've created this elaborate scheme to create small unique identifiers for
external transactions. It is built on a compact representation of the
current_timestamp(6) + 2 peices of data (varchar(50)) + a sequence number. I'd
like the option of restarting the sequence at some later date, but I don't want
to replicate any IDs. 

To shorten the identifier I thought it might be a good idea to run an MD5
digest using the functionality from contrib/pgcrypto on it. ("thought" and
"might" are the key words there...)

I just need another (thousand?) set of eyes to see if a) I've missed anything,
b) There's a better way... :)

-- Sequence: universal_sq
CREATE SEQUENCE universal_sq INCREMENT 1 MINVALUE 1 MAXVALUE
9223372036854775807 CACHE 1;

-- Creates an text/interger representation of the current time
-- with microseconds.
CREATE FUNCTION "dtid"() RETURNS "text" AS '
  DECLARE
    tstz timestamptz;
    dtid text;
  BEGIN
    tstz := current_timestamp;
    dtid := (((tstz::ABSTIME::INT4)/100 * 100000000::INT8) +
date_part(\'microseconds\',tstz))::INT8::TEXT;
    RETURN dtid;
  END;' LANGUAGE 'plpgsql';

SELECT encode(digest(dtid() || 'value1' || 'value2' ||
nextval('universal_sq')::text, 'md5'),'hex');



__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/

In response to

  • Re: DAFS? at 2002-10-31 16:06:04 from scott.marlowe

pgsql-general by date

Next:From: Doug McNaughtDate: 2002-10-31 18:19:47
Subject: Re: Inserting streamed data
Previous:From: Kevin OldDate: 2002-10-31 18:11:49
Subject: Inserting streamed data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group