Re: Getting a random row

On Tue, Oct 13, 2009 at 9:17 AM, Shaul Dar <shauldar@gmail.com> wrote:
> Hi,
>
> I am running performance simulation against a DB. I want to randomly pull
> different records from a large table. However the table has no columns that
> hold sequential integer values (1..MAX), i.e. the columns all have "holes"
> (due to earlier filtering). Also PG does not have a concept of an
> auto-increment pseudo-column like Oracle's "rownum". Any suggestions?

If what you're trying to do is emulate a real world app which randomly
grabs rows, then you want to setup something ahead of time that has a
pseudo random order and not rely on using anything like order by
random() limit 1 or anything like that.  Easiest way is to do
something like:

select id into randomizer from maintable order by random();

then use a cursor to fetch from the table to get "random" rows from
the real table.