Summary: this tutorial shows you how to develop a user-defined function that generates a random number between two numbers.
PostgreSQL provides the random() function that returns a random number between 0 and 1. The following statement returns a random number between 0 and 1.
1 | SELECT random(); |
1 2 3 4 | random ------------------- 0.867320362944156 (1 row) |
To generate a random number between 1 and 10, you use the following statement:
1 | SELECT random() * 10 + 1 AS RAND_1_10; |
1 2 3 4 | rand_1_10 ------------------ 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:
1 | SELECT floor(random() * 10 + 1)::int; |
1 2 3 4 | floor ------- 9 (1 row) |
Generally, to generate a random number between two integers l and h, you use the following statement:
1 | SELECT floor(random() * (h-l+1) + l)::int; |
You can develop a user-defined function that returns a random number between two numbers l and h:
1 2 3 4 5 6 | CREATE OR REPLACE FUNCTION random_between(low INT ,high INT) RETURNS INT AS$$ BEGIN RETURN floor(random()* (high-low + 1) + low); END; $$ language 'plpgsql' STRICT; |
The following statement calls the random_between() function and returns a random number between 1 and 100:
1 | SELECT random_between(1,100); |
1 2 3 4 | random_between ---------------- 81 (1 row) |
If you want to get multiple random numbers between two integers, you use the following statement:
1 2 | SELECT random_between(1,100) FROM generate_series(1,5); |
1 2 3 4 5 6 7 8 | 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.