Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f3QCDT398210 for ; Thu, 26 Apr 2001 08:13:29 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3ODtt325564 for ; Tue, 24 Apr 2001 09:55:55 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f3ODTrd59108 for pgsql-sql@postgresql.org; Tue, 24 Apr 2001 09:29:53 -0400 (EDT) (envelope-from news) From: Vasilis Samoladas X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Using Random Sequence as Key Date: 24 Apr 2001 08:29:51 -0500 Organization: Computer Sciences, University of Texas Lines: 51 Message-ID: <9c3v4f$kus$1@crom.cs.utexas.edu> References: <9bdjag$faj$1@news.tht.net> X-Complaints-To: scrappy@hub.org User-Agent: tin/1.4.1-19991201 ("Polish") (UNIX) (Linux/2.2.19 (i686)) To: pgsql-sql@postgresql.org X-Archive-Number: 200104/397 X-Sequence-Number: 2755 Hello Bernardo, there is a somewhat mathematical solution to your problem. You can use a unique key, say 0<= k < MAX for each record, and a second key which is computed from the first, say f(k), which will give you a unique permutation of the numbers 0,...,MAX-1. It will not be random, but it will be hard for someone to figure out the original unique key k, without knowning your method. Here is one way to compute f(k): (math follows, beware!) The Fibonacci numbers are a sequence defined as follows: F(0) = 0, F(1) = 1, F(n+2) = F(n+1)+F(n) (for n>=0). Now, for every fixed number N>=2, you can define a permutation f(k) of the numbers 0,...,F(N)-1 as follows: f(k) = (k*F(N-1)) mod F(N) For example, if N=6, we have F(6)=8 and F(5)=5, so for 0<= k <8, f(k) = 5*k mod 8 is a permutation. In particular, k = 0 1 2 3 4 5 6 7 f(k) = 0 5 2 7 4 1 6 3 So, since you need approximately 90000 numbers (10000 to 99999), you can use N=25, which will give you F(25)=75025 and F(24)=46368. Thus, you can have 75025 (slightly fewer than 90000) unique values, by translating k into f(k) = 46368*k mod 75025 Unless your users know number theory, this should work for you. Vasilis Bernardo de Barros Franco wrote: : Hello, I was wondering if noone can help me maybe someone could at least : give me some directions where to look for info or where to ask: : I wanted to index a table by a random key. Exemplifying, when a insert is : made, the id value is automatically filled with a random number between : 10000 and 99999. I'm pretty new in pgsql so I could use a howto or : something, and I repeat, if you don't know how, directions to where I can : find info on that or where I can ask someone that might know would be : enough. : Thank you