Topic: [Solved] RAND() not working in PostgreSQL

Hey all,

I have my development database on MySQL and my production on PostgreSQL.

Now is the RANDOM function different in those databases. The following code crashes on PostgreSQL

product.category_id = Category.find(:first, :order => "RAND()").try(:id)

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

does somebody has a solution for this? I like to continue using mysql as my development database..

Thanks in advanced

Last edited by mokkol (2010-01-12 11:18:36)

Re: [Solved] RAND() not working in PostgreSQL

Category.first( :order => 'random()') should work fine, however to keep it database agnostic, try adding a method like this to your model:

def self.random
  ids = connection.select_all("SELECT id FROM categories")
  find(ids[rand(ids.length)]["id"].to_i) unless ids.blank?
end

There are obviously other ways to do it and im sure more optimized methods, but this is an idea. Example taken from http://almosteffortless.com/2007/12/04/ … -in-rails/ (not my site). check it out for more info.

Re: [Solved] RAND() not working in PostgreSQL

set a constant in /environments/development.rb and production.rb respectivly, which contains "mysql" or "postgresql"

DB_ADAPTER = "mysql"

then add this to a file in /lib:

/lib/agnostic_random.rb
module AgnosticRandom
  def random
    case DB_ADAPTER
      when "mysql" then "RAND()"
      when "postgresql" then "RANDOM()"
    end
  end
end

/initializers/extend_ar.rb (name doesn't matter)
ActiveRecord::Base.extend AgnosticRandom

and use this method in your querys

product.category_id = Category.find(:first, :order => random ).try(:id)

Re: [Solved] RAND() not working in PostgreSQL

Thanks for your reply!

That triggers first a query to fetch all the ids of the whole table. Not a safe way when you have lots of records in the database.

They provide the same solution. order by rand() but that doesnt work with postgresql

Re: [Solved] RAND() not working in PostgreSQL

Thanks Duplex! that will help!

Re: [Solved] RAND() not working in PostgreSQL

I believe you are going to find that using order by random is going to give you the same, if not a longer execute time than just selecting the ID's alone. If you perform an analyze/explain on both methods, you should see a lower cost when not using order by. This is because even when using order by, the database has to first get ALL records in the table, and then sort them.

I am no database expert, so if anyone has alternative information on this, please jump in.

I guess the bottom line is, neither method will be optimal once your table reaches a significant size.

Re: [Solved] RAND() not working in PostgreSQL

Freezzo, i guess you are right. In the background they will do the same. I am not an expert either smile but i think you have to handle this kind of stuff on the database side instead of on the Ruby side. I am not going to test this with lots of records to compare tho smile

The only speed boost you will get by this is that you dont have 2 requests to the database but 1, although it is a more expensive request.

Re: [Solved] RAND() not working in PostgreSQL

Whatever methods you decide upon, I hope works well for your project. I just provide an alternative approach should you need it smile