Working with the random() function in PostgreSQL can be a bit tricky if you’re trying to populate a table.

The random() function in PostgreSQL will return a number between 0 and 1 like so:

SELECT RANDOM();
      random       
-------------------
 0.115072432027698
(1 row)

If you’re trying to get a whole number from random(), you can use some multiplication and the round() function to let random() work for you. Say you wanted to get a random number from 0-100:

SELECT ROUND(RANDOM() * 100);
 round 
-------
    22
(1 row)

For the project I’m working on, we wanted to pre-populate some birthdays with random dates. I tried using a combination of the datetime functions with an interval and random() and couldn’t quite get there. Searching around on Google didn’t provide too many useful results so I turned to the wonderful folks in the #postgresql chat at irc.freednode.net. Using a combination of the above and the suggestions from the chat room, I was able to come up with a query that did what I wanted:

SELECT NOW() - '1 year'::INTERVAL * ROUND(RANDOM() * 100);
           ?COLUMN?           
------------------------------
 1987-01-20 11:10:34.26494-07
(1 row)

That means we could easily update our tables for people with random birthdays with a single update query:

UPDATE table_name SET birthday = NOW() - '1 year'::INTERVAL * ROUND(RANDOM() * 100);

Hopefully you found this post useful. If you did, please consider subscribing to my feed.

This entry was posted on Wednesday, January 20th, 2010 at 11:26 am and is filed under Geekery. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

• 5 & 5 – A List of Things I Feel When at Work (away from her)
• Speed Comparison of strlen() VS empty() in PHP Shows empty() is Faster
• Writing Code Rule #1: Learn to Have Confidence to Break Stuff (and Firefox Keyword Search)