"Arnau Rebassa" <[EMAIL PROTECTED]> writes: > select * from messages order by random() limit 1; > > in the table messages I have more than 200 messages and a lot of times, the > message retrieved is the same. Anybody knows how I could do a more "random" > random?
What OS is this? Postgres is just using your OS's random()/srandom() calls. On some platforms these may be poorly implemented and not very random. However of the various choices available I think random/srandom are a good choice. I'm surprised you're finding it not very random. Incidentally, are you reconnecting every time or is it that multiple calls in a single session are returning the same record? It ought not make a difference as Postgres is careful to seed the random number generator with something reasonable though. In a quick test of my own on linux with glibc 2.3.2.ds1 (no, I have no idea what the ds1 means) It seems fairly random to me: test=> create table test4 as (select (select case when b.b then a else a end from test order by random() limit 1) as b from b limit 1000); SELECT test=> select count(*),b from test4 group by b; count | b -------+--- 210 | 5 195 | 4 183 | 3 203 | 2 209 | 1 (5 rows) And the same thing holds if I test just the low order bits too: test=> create table test4 as (select (select case when b.b then a else a end from test order by random() limit 1) as b from b limit 1000); SELECT test=> select count(*),b from test4 group by b; count | b -------+--- 249 | 4 241 | 3 259 | 2 251 | 1 (4 rows) -- greg ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly