Re: Getting a random row - Mailing list pgsql-performance

Shaul Dar, 13.10.2009 17:17:
> Also PG does not have a concept of an auto-increment pseudo-column
> like Oracle's "rownum". Any suggestions?

Yes it does (at least 8.4)

SELECT row_number() over(), the_other_columns...
FROM your_table

So you could do something like:

SELECT *
FROM (
  SELECT row_number() over() as rownum,
         the_other_columns...
  FROM your_table
) t
WHERE t.rownum = a_random_integer_value_lower_than_rowcount;

Thomas