Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3HF8Eu49243 for ; Tue, 17 Apr 2001 11:08:14 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 40649; Tue, 17 Apr 2001 08:08:09 -0700 From: "Josh Berkus" Subject: Re: Re: Using Random Sequence as Key To: "Bernardo de Barros Franco" , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Tue, 17 Apr 2001 08:08:09 -0700 Message-ID: In-Reply-To: <9bfiro$qma$1@news.tht.net> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200104/267 X-Sequence-Number: 2617 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