Postgres Random Number

Random numbers are crucial in various aspects of database management and analysis. For example, in databases, random numbers are fundamental to generate the test data, select random records, distribute the workloads, and enhance the data security.

This tutorial explores the different techniques to generate random numbers in PostgreSQL, ranging from basic functions to more advanced approaches using seeds, ordering, series generation, extensions, and custom functions.

Method 1: Using the Random() Function

The most common method to generate a random number in PostgreSQL is the RANDOM() function. The function generates a random value between 0 and 1. We can then scale and shift the generated weight based on your requirements.

Consider the following example that generates a random value between 0 and 1 using the random() function:

SELECT RANDOM();

The resulting value is as follows:

random
----------------------
0.010084709124918101

We can also provide a given range and allow the function to limit the randomly generated value between the defined range.

The query syntax is as follows:

SELECT FLOOR(random() * (max - min + 1)) + min;

Consider the following example:

SELECT FLOOR(random() * (10 - 5 + 1)) + 5;

This should return a value between 10 and 5.

?column?
----------
7
(1 row)

Using the Random Function with Seed Value

The function also allows us to specify a seed value, ensuring that we can produce the same sequence of random values. This is very useful when generating the same random numbers multiple times.

The following example demonstrates how we can use the random() function to generate a random value between 1 and 10 with a seed value 50:

select floor((random() * 10) + 1) as random_number from (select setseed(0.50) from generate_series(1,10)) as seed_table;

This should return a sequence of similar values as follows:

random_number
---------------
10
10
...
10
10
(10 rows)

Using the Random Function with Order By

We can also use the RANDOM() function in conjunction with the ORDER BY clause to generate random rows from a table. This is useful when we need to select random records from a table.

An example is as follows:

SELECT *
FROM <table_name>
ORDER BY RANDOM()
LIMIT 1;

Method 2: Using the Generate_Series() Function

In PostgreSQL, we also have access to the generate_series() function which allows us to generate a series of values. As demonstrated in the following example, we can combine this function with the random() function to generate a series of random values:

SELECT FLOOR(random() * 100) + 1 AS random_number
FROM generate_series(1, 10);

Resulting Output:

random_number
---------------
83
13
17
65
89
15
16
52
78
49
(10 rows)

Method 3: Using the UUID-OSSP Extension

We can also use the UUID-OSSP extension in PostgreSQL to generate a set of unique UUID values. An example is as follows:

SELECT uuid_generate_v4();

Output:

uuid_generate_v4
--------------------------------------
5dbc3246-4c79-4bd8-9bf2-340871cfe496
(1 row)

Method 4: Using the PLSQL Language

In some cases, you may need a custom logic to generate a random number in your database. In such a scenario, you can use the PLSQL language to define a custom function to generate random integers. An example is as follows:

CREATE OR REPLACE FUNCTION generate_random_integer()
RETURNS integer AS $$
DECLARE
random_number integer;
BEGIN
random_number := FLOOR(random() * 10) + 1;
RETURN random_number;
END;
$$ LANGUAGE plpgsql;

We can then call the generate_random_integer() function which should return a random integer value as defined in our function logic.

You can customize the function to perform the actions that you need as necessary.

Conclusion

We learned how we can use the various methods and techniques in PostgreSQL to generate a random integer.