Re: Re: Re: Re: RANDOM function?

On Tue, 31 Jul 2001, Einar Karttunen wrote:
> On Tue, 31 Jul 2001, Neal Lindsay wrote:
>
> > I think that what you want to do is create a trigger (or rule?) that pulls
> > up your random number and then checks to see if it exists already. I
> > haven't used triggers yet, but it should not be too hard. Also, what
> > happens when you declare a column unique, and a function like random()
> > tries to give it a non-unique number?
> >
> This can be accomplished very easily.
>
> CREATE TABLE tbl (
> col integer default (random()*2147483647) unique,
> col2 text
> );
>
> INSERT INTO tbl (col2) VALUES ('the value of col2');
>
> the insert will fail if the number is not unique then just perform it
> anew.

The only thing I would add, is your multiplier (2147...) must
always be big, with respect to how many times you want to draw
this random number without replacement. If you were wanting
to draw 2000000000 random numbers, this method would bog
down in rejections (insert failures) towards the end.

Gord

Matter Realisations http://www.materialisations.com/
Gordon Haverland, B.Sc. M.Eng. President
101 9504 182 St. NW Edmonton, AB, CA T5T 3A7
780/481-8019 ghaverla @ freenet.edmonton.ab.ca
780/993-1274 (cell)