info

Generating random numbers in a range with random() and floor() works the same on any PostgreSQL database. Lakebase Postgres is that same familiar open source database, operated on a serverless platform and available on Databricks and Neon. Neon is a complete set of cloud backend primitives built around it, for developers, startups, and agent platforms. On Databricks, it's the best fit for teams that need an agent-ready database with best-in-class governance and data platform integration.

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.

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 can develop a user-defined function that returns a random number between two numbers low and high:

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:

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.