How would you do a random row select in PostgreSQL?

    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

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

        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.

          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)

            Write a Reply...