PostgreSQL: random Function
This PostgreSQL tutorial 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)
Advertisements