basically, the user_mapper maps ids from _user, so that if a user is deleted, random user can still be picked without worrying about a number being generated that doesnt match an existing user

user_mapper

create table user_mapper (id serial, user_id int);

 create index on user_mapper (id);

create index on user_mapper (user_id);

create or replace function user_mapper_insert()                                                                       
Returns trigger as                                                                                                                    
$body$                                                                                                                               
begin                                                                                                                                
insert into user_mapper (user_id) values (new.id);                                                                                   
return new;                                                                                                                          
end;                                                                                                                                 
$body$                                                                                                                               
language plpgsql;

create  trigger user_insert                                                                                            
after insert on _user                                                                                                                
for each row                                                                                                                         
execute function user_mapper_insert(); 

 CREATE OR REPLACE FUNCTION user_mapper_update()                                                                       
RETURNS trigger AS                                                                                                                   
$BODY$                                                                                                                               
BEGIN                                                                                                                                
IF NEW.id <> OLD.id THEN                                                                                                             
UPDATE user_mapper SET user_id = NEW.id WHERE user_id = OLD.id;                                                                      
END IF;                                                                                                                              
RETURN OLD;                                                                                                                          
END;                                                                                                                                 
$BODY$                                                                                                                               
LANGUAGE plpgsql;

CREATE TRIGGER user_mapper_update                                                                                     
 BEFORE UPDATE                                                                                                                       
ON _user                                                                                                                             
FOR EACH ROW                                                                                                                         
execute function user_mapper_update(); 

CREATE OR REPLACE FUNCTION user_mapper_delete()                                                                       
RETURNS trigger AS                                                                                                                   
$BODY$                                                                                                                               
BEGIN                                                                                                                                
DELETE FROM user_mapper WHERE user_id = OLD.id;                                                                                      
UPDATE user_mapper SET id = id - 1 WHERE user_id > OLD.id;                                                                           
RETURN OLD;                                                                                                                          
END;                                                                                                                                 
$BODY$                                                                                                                               
LANGUAGE plpgsql;

CREATE TRIGGER user_delete                                                                                            
BEFORE DELETE                                                                                                                        
ON _user                                                                                                                             
FOR EACH ROW                                                                                                                         
execute function user_mapper_delete();  


ACTUAL QUERY TO GET RANDOM ID:

SELECT user_mapper.user_id
FROM user_mapper
WHERE id = (
	SELECT CASE WHEN id = 0 THEN 1 ELSE id END
	FROM (SELECT ROUND(RANDOM() * (SELECT MAX(id) FROM user_mapper)) as id) as r
)