pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Bernardo de Barros Franco <electric_csf@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Re: Using Random Sequence as Key
Date: Tue, 17 Apr 2001 08:08:09 -0700
Message-ID: <web-40649@davinci.ethosmedia.com> (raw)
In-Reply-To: <9bfiro$qma$1@news.tht.net>

Bernardo,

> I needed the random field because if I use serial and the user gets a
> 34203
> he's sure that 34202 exists, and that (probably, there where 34202
> inserts
> before him (or at least an offset + some)). Using a random just makes
> the
> user totally blind.
> As I said I could use a serial for indexing the table but I NEED the
> random
> field and I need to to be unique since all the queries will be using
> it as a
> search parameter.
> If inserting this way is slow it's not such a big deal since it's a
> small db
> and inserts are seldom made.
> Thanks in advance for any help.

Here's another suggestion for you then:

1. Add a sequence "Sales_sq"

1. write a custom function for new id numbers:
CREATE FUNCTION new_sales_id() RETURNS INT4 AS '
DECLARE
	timeportion VARCHAR;
	serialportion INT4;
BEGIN
	timeportion := to_char(current_timestamp, ''ms'');
-- (or whatever the abbreviation for 2-digit milliseconds is)
	serialportion := 100*(nextval(''sales_seq''));
	RETURN CAST(to_number(timeportion) AS INT4) + serialportion;
END;

3. Then set the id column to default to this new function.

This would give you (after you correct my mistakes) a number, the first
X digits of are Serial, and the last 2 digits based on the server's
internal clock.  Thus, the numbers would *not* be sequential, and would
appear fairly random, but would be unique *without* and expensive check
for that value anywhere in the table for each insert.

-Josh Berkus






______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



view thread (12+ messages)  latest in thread

Message-ID: <web-40649@davinci.ethosmedia.com>
Permalink:  ../web-40649@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-40649@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, electric_csf@hotmail.com
  Subject: Re: Re: Using Random Sequence as Key
  In-Reply-To: <web-40649@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox