PostgreSQL provides therandom() function that returns a random number between 0 and 1. The following statement returns a random number between 0 and 1.

SELECT random();

random------------------- 0.867320362944156(1 row)

To generate a random number between 1 and 11, you use the following statement:

SELECT random()* 10 + 1 AS RAND_1_11;

rand_1_11------------------ 7.75778411421925(1 row)

If you want to generate the random number as an integer, you apply the floor() function to the expression as follows:

SELECT floor(random()* 10 + 1)::int;

floor-------     9(1 row)

Generally, to generate a random number between two integers low and high, you use the following statement:

SELECT floor(random()* (high-low+1)+ low)::int;

You candevelop a user-defined function that returns a random number between two numberslow andhigh:

CREATE OR REPLACE FUNCTION random_between(lowINT ,highINT)   RETURNS INT AS$$BEGIN   RETURN floor(random()* (high-low+ 1)+ low);END;$$language 'plpgsql' STRICT;

The following statement calls therandom_between() function and returns a random number between 1 and 100:

SELECT random_between(1,100);

random_between----------------             81(1 row)

If you want to get multiple random numbers between two integers, you use the following statement:

SELECT random_between(1,100)FROM generate_series(1,5);

random_between----------------             37             82             19             92             43(5 rows)

In this tutorial, you have learned how to generate a random number between a range of two numbers.

Last updated onFebruary 17, 2024