PostgreSQL: random Function

In this PostgreSQL post explains how to use the PostgreSQL random function with syntax and examples.

Description

The PostgreSQL random function can be used to return a random number or a random number within a range.

Syntax

The syntax for the random function in PostgreSQL is:

random( )

Parameters or Arguments

There are no parameters or arguments for the random function.

Note

• The random function will return a value between 0 (inclusive) and 1 (exclusive), so value >= 0 and value 1.
• Use the setseed function to set the seed for the random function.
• The random function will return a completely random number if no seed is provided (seed is set with the setseed function).
• The random function will return a repeatable sequence of random numbers each time a particular seed value is used (seed is set with the setseed function).

Random Decimal Range

To create a random decimal number between two values (range), you can use the following formula:

SELECT random()*(b-a)+a;

Where a is the smallest number and b is the largest number that you want to generate a random number for.

SELECT random()*(25-10)+10;

The formula above would generate a random decimal number >= 10 and 25. (Note: this formula will never return a value of 25 because the random function will never return 1.)

Random Integer Range

To create a random integer number between two values (inclusive range), you can use the following formula:

SELECT floor(random()*(b-a+1))+a;

Where a is the smallest number and b is the largest number that you want to generate a random number for.

SELECT floor(random()*(25-10+1))+10;

The formula above would generate a random integer number between 10 and 25, inclusive.

Applies To

The random function can be used in the following versions of PostgreSQL:

• PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4

Example of Random Number

Let's explore how to use the random function in PostgreSQL to generate a random number >= 0 and 1.

For example:

postgres=# SELECT random();
      random
-------------------

 0.576233202125877
(1 row)

Although the random function will return a value of 0, it will never return a value of 1. It will always return a value smaller than 1.

Example of Random Decimal Range

Let's explore how to use the random function in PostgreSQL to generate a random decimal number between two numbers (ie: range).

For example, the following would generate a random decimal value that is >= 1 and 10 (Note: it will never return a value of 10):

postgres=# SELECT random()*(10-1)+1;
     ?column?
-------------------

 6.65084521705285
(1 row)

The following example would generate a random decimal value >= 25 and 40 (Note: it will never return a value of 40):

postgres=# SELECT random()*(40-25)+25;
     ?column?
------------------

 37.7279848000035
(1 row)

Example of Random Integer Range

Let's explore how to use the random function in PostgreSQL to generate a random integer number between two numbers (ie: inclusive range).

For example, the following would generate a random integer value between 1 and 10:

postgres=# SELECT floor(random()*(10-1+1))+1;
 ?column?
----------

     8
(1 row)

The following example would generate a random integer value between 25 and 40:

postgres=# SELECT floor(random()*(40-25+1))+25;
 ?column?
----------

    36
(1 row)