I have a table in postgres that contains couple of millions of rows. I have checked on the internet and I found the following
SELECT myid FROM mytable ORDER BY RANDOM() LIMIT 1;
It works, but it's really slow... is there another way to make that query, or a direct way to select a random row without reading all the table? By the way 'myid' is an integer but it can be an empty field.
You might want to experiment with OFFSET, as in
SELECT myid FROM mytable OFFSET floor(random() * N) LIMIT 1;
The N is the number of rows in mytable. You may need to first do a SELECT COUNT(*) to figure out the value of N.
Update (by Antony Hatchkins)
You must use floor here:
SELECT myid FROM mytable OFFSET floor(random() * N) LIMIT 1;
Consider a table of 2 rows; random()*N generates 0 <= x < 2 and for example SELECT myid FROM mytable OFFSET 1.7 LIMIT 1; returns 0 rows because of implicit rounding to nearest int.