Hi! I have to select a random row from a table where primary key isn't continuous (some rows have been deleted). Postgres just seems to do something strange with my method.
-- -- Use the order by desc limit 1 -trick to get maximum value -- CREATE OR REPLACE FUNCTION max_uid() RETURNS int4 AS 'SELECT uid FROM users WHERE status = ''a'' ORDER BY uid DESC LIMIT 1' LANGUAGE 'sql'; -- -- Choose a random point between 0 and max_uid and select the first -- value from the bigger part -- CREATE OR REPLACE FUNCTION random_uid() RETURNS int4 AS 'SELECT uid FROM users u WHERE u.status = ''a'' AND uid >= cast(cast(max_uid() - 1 AS FLOAT) * random() AS INTEGER) ORDER BY uid ASC LIMIT 1' LANGUAGE 'sql'; -- -- testing and looks good -- galleria=> SELECT max_uid(); max_uid --------- 126263 -- -- testing... -- galleria=> SELECT random_uid(), random_uid(), random_uid(), random_uid(), random_uid(); random_uid | random_uid | random_uid | random_uid | random_uid ------------+------------+------------+------------+------------ 322 | 601 | 266 | 427 | 369 ... but what is this? Values seem to vary from 0 to ~1000. Not from 0 to 126263!! How about doing some manual work... -- -- Testing split point selection -- galleria=> SELECT cast(cast(max_uid() - 1 AS FLOAT) * random() AS INTEGER); int4 ------- 43279 -- -- And inserting split point manually -- galleria=> SELECT uid FROM users u WHERE u.status = 'a' AND uid >= 43279 ORDER BY uid ASC LIMIT 1; uid ------- 43284 Works just fine! Is there any explanation for this strange behavior or are there better ways to select a random row? I'm using PG 8.0 b2. Plan for the query is: Limit (cost=0.00..5.19 rows=1 width=4) -> Index Scan using users_pkey on users u (cost=0.00..69145.26 rows=13329 width=4) Filter: ((status = 'a'::bpchar) AND (uid >= ((((max_uid() - 1))::double precision * random()))::integer)) |\__/| ( oo ) Kari Lavikka - [EMAIL PROTECTED] __ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _ "" ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster