Random Select in PostgreSQL

[ryanation]

How would you do a random row select in PostgreSQL?

[gigowiz]

I had a similar problem but it may be a bit different for you depending on the size of the table, what you're doing with the data, etc. I selected every row and inserted the data into a temporary table along with an extra field that held a random number. Then I selected from the temporary table ordered by the random number field. Hope this helps.

Hank

[ryanation]

I just want to display a random record from a table to a web page.

[ultraslacker]

Well, it can be slow, as you have to generate a random number for all the rows -

SELECT random() AS sort, * FROM table order by sort LIMIT 1;

You could probably improve on this.

[Sxooter]

Here's the quickest way to do it:

select count() from table;
use php to create a random number from 0 to the count-1 (i.e. rand(0,$count-1)
select from table limit 1 offset $rand;

Generally speaking that's faster than

select * from table order by random() (which IS supported in 7.2.x)