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

Re: [HACKERS] RE: [SQL] random tuple

From: "Thomas G(dot) Lockhart" <lockhart(at)alumni(dot)caltech(dot)edu>
To: "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com>
Cc: Mauro Bartolomeoli <mbarto(at)novacomp(dot)it>, pgsql-sql(at)postgreSQL(dot)org, PostgreSQL Hackers Mailing List <pgsql-hackers(at)hub(dot)org>
Subject: Re: [HACKERS] RE: [SQL] random tuple
Date: 1998-10-09 04:43:55
Message-ID: 361D948B.9612C41C@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
> > >> Is there any way to select a random row from a table?
> > > I'd look at using cursors and random().
> > Do you (or someone else) know how to use the following
> > PostgreSQL functions: oidrand(oid,int4) ,oidsrand(int4)?
>     oidsrand(int4) -- seeds the random number generator for oidrand
>     oidrand(oid, int4) -- returns a psudo-random oid

The regression test uses oidrand(), which is where I stumbled across it.
The behavior is that oidrand() returns a boolean true/false with an
inverse probability specified by the second argument. For example, given
a table t with 100 entries, and the query

  select * from t where oidrand(oid, 10)

will return, on average, 10 (10%) of the entries at random. The function
is called 100 times in the course of the query, and uses random() or
something similar to decide whether to return true or false for any
particular instance.

  select * from t where oidrand(oid, 1)

will, on average, return all entries (1/1 = 100%).

  select * from t where oidrand(oid, 100)

will, on average, return 1 entry (1/100 = 1%) so sometimes will return
one, zero, or two entries, and occasionally return more than two
entries.

It's pretty random, probably with a Poisson distribution depending on
what you are asking for.

Presumably oidsrand() allows one to change the seed to keep the
pseudo-random results from repeating from one run to the next. But I
haven't looked into it.

                   - Tom

In response to

pgsql-hackers by date

Next:From: Thomas G. LockhartDate: 1998-10-09 05:00:32
Subject: Re: [HACKERS] man page installation patch
Previous:From: Bruce MomjianDate: 1998-10-09 04:29:23
Subject: Open 6.4 items

pgsql-sql by date

Next:From: Blaise LaurentDate: 1998-10-11 09:27:37
Subject: french caracters in Postgresql database ?
Previous:From: Terry MackintoshDate: 1998-10-08 18:06:00
Subject: Re: [ADMIN] RE: [HACKERS] Re: [COMMITTERS] 'pgsql/doc/src/sgml protocol.sgml'

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