Random Dates and Numbers in PostgreSQL with the RANDOM() Function

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.