In PostgreSQL, the random() function returns a pseudo-random value in the range 0.0 <= x < 1.0.

It uses a simple linear congruential algorithm, which is one of the oldest and best-known pseudo-random number generator algorithms.

A pseudo-random number is a number that appears to be random, but is not truely random. A pseudo-random number is not truely random because its value was generated by a known seed. However, it will appear to be random if the user has no knowledge of the seed or algorithm that created it.

Therefore, pseudo-random numbers are often considered good enough for many applications.

Example

Here’s an example of generating a pseudo-random number with the random() function.

SELECT random();

Result:

0.625357600199532

The result will obviously be different each time you call it.

Here’s another example where I call the function three times in the same statement.

SELECT 
  random(),
  random(),
  random();

Result:

 random            | random              | random
-------------------+---------------------+--------------------
 0.594431747016209 | 0.22816249693650903 | 0.7168820259873314

Random Number Between 1 and 10

Here’s an example of generating a positive number between 0 and 10.

SELECT random() * 9 + 1;

Result:

4.564859004063727

Just to be clear, this generates a random number that is >= 1 and < 10.

Random Integer

You can use a function such as trunc() or floor() to return the random number as an integer.

SELECT 
  trunc(random() * 9 + 1),
  floor(random() * 9 + 1);

Result:

 trunc | floor
-------+-------
     1 |     8

Return Random Rows

You can use random() in an ORDER BY clause of a database query to return random rows.

Here’s an example that queries the pagila sample database.

SELECT 
  film_id,
  title
FROM film 
ORDER BY random() LIMIT 5;

Result:

 film_id |        title        
---------+---------------------
     116 | CANDIDATE PERDITION
     806 | SLEEPY JAPANESE
     892 | TITANIC BOONDOCK
     826 | SPEED SUIT
     612 | MUSSOLINI SPOILERS

And here’s what I get if I run it again:

 film_id |       title        
---------+--------------------
     450 | IDOLS SNATCHERS
     827 | SPICE SORORITY
     593 | MONTEREY LABYRINTH
     529 | LONELY ELEPHANT
     591 | MONSOON CAUSE

If you have a large table, and you need to return all rows (or lots of rows) you might want to modify your query to something like this:

SELECT *
FROM film 
WHERE film_id IN 
  (SELECT film_id FROM film ORDER BY random() LIMIT 5);

Create Repeatable Random Numbers

Postgres also has a setseed() function that allows you to set a seed for subsequent random() calls within the same session.

You can use setseed() to generate repeatable random() calls.

See How Setseed() Works in Postgres for examples.