Postgres random() function

The Postgres random() function generates random floating point values between 0.0 and 1.0. Starting with Postgres 17, it also supports generating random integers or decimals within a specified range using random(min, max) syntax.

It's particularly useful for creating some sample data, usage in simulations, or introducing randomness in queries for applications like statistical sampling and testing algorithms.

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Function signatures

The random() function has the following signatures:

random() -> double precision
random(min integer, max integer) -> integer      -- Added in Postgres 17
random(min bigint, max bigint) -> bigint        -- Added in Postgres 17
random(min numeric, max numeric) -> numeric      -- Added in Postgres 17

The first form returns a uniformly distributed random value between 0.0 (inclusive) and 1.0 (exclusive).

Starting from Postgres 17, the function also accepts range parameters:

For integer types, it returns a random integer between min and max (inclusive)
For numeric types, it returns a random decimal number between min and max (inclusive). The result will have the same number of decimal places as the input parameter with the highest precision.

Example usage

SELECT random(); -- Generates a random floating point number between 0.0 and 1.0
-- 0.555470146570157

SELECT random(1, 6); -- Generates a random integer between 1 and 6
-- 4

SELECT random(1.5, 3.54); -- Generates a random decimal number between 1.5 and 3.54 with 2 decimal places precision
-- 2.66

Basic random number generation

Let's create a table of simulated sensor readings with random values:

CREATE TABLE sensor_readings (
  id SERIAL PRIMARY KEY,
  sensor_name TEXT,
  temperature NUMERIC(5,2),
  humidity NUMERIC(5,2),
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO sensor_readings (sensor_name, temperature, humidity)
SELECT
  'Sensor-' || generate_series,
  20 + (random() * 15)::NUMERIC(5,2),  -- Temperature between 20°C and 35°C
  40 + (random() * 40)::NUMERIC(5,2)   -- Humidity between 40% and 80%
FROM generate_series(1, 5);

SELECT * FROM sensor_readings;

The generate_series() function is used to generate a series of integers from 1 to 5, which is then used to create the sensor names. Then, random() is used to generate random temperature and humidity values within specific ranges.

id | sensor_name | temperature | humidity |         timestamp
----+-------------+-------------+----------+----------------------------
  1 | Sensor-1    |       26.16 |    76.85 | 2024-06-23 10:34:03.627556
  2 | Sensor-2    |       31.49 |    44.88 | 2024-06-23 10:34:03.627556
  3 | Sensor-3    |       30.62 |    49.94 | 2024-06-23 10:34:03.627556
  4 | Sensor-4    |       23.32 |    79.20 | 2024-06-23 10:34:03.627556
  5 | Sensor-5    |       34.33 |    50.39 | 2024-06-23 10:34:03.627556
(5 rows)

Random integer within a range

Let's simulate a dice game where each player rolls two dice, and we calculate the total:

CREATE TABLE dice_rolls (
  roll_id SERIAL PRIMARY KEY,
  player_name TEXT,
  die1 INTEGER,
  die2 INTEGER,
  total INTEGER
);

INSERT INTO dice_rolls (player_name, die1, die2, total)
SELECT
  'Player-' || generate_series,
  random(1, 6),  -- Random integer between 1 and 6
  random(1, 6),  -- Random integer between 1 and 6
  0  -- We'll update this next
FROM generate_series(1, 5);

UPDATE dice_rolls
SET total = die1 + die2;

SELECT * FROM dice_rolls;

This simulates 5 players each rolling two dice, with random values between 1 and 6 for each die. Notice how we can now use the simpler random(1, 6) syntax instead of the more complex 1 + floor(random() * 6)::INTEGER typically used in earlier versions of Postgres.

roll_id | player_name | die1 | die2 | total
---------+-------------+------+------+-------
       1 | Player-1    |    6 |    1 |     7
       2 | Player-2    |    1 |    3 |     4
       3 | Player-3    |    5 |    1 |     6
       4 | Player-4    |    6 |    2 |     8
       5 | Player-5    |    5 |    6 |    11
(5 rows)

Other examples

Using random() for sampling

Suppose we have a large table of customer data and want to select a random sample for a survey:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT
);

-- Populate the table with sample data
INSERT INTO customers (name, email)
SELECT
  'Customer-' || generate_series,
  'customer' || generate_series || '@example.com'
FROM generate_series(1, 1000);

-- Select a random 1% sample
SELECT *
FROM customers
WHERE random() < 0.01;

This query selects approximately 1% of the customers randomly by filtering for rows where random() is less than 0.01.

id  |     name     |          email
-----+--------------+-------------------------
  18 | Customer-18  | customer18@example.com
 349 | Customer-349 | customer349@example.com
 405 | Customer-405 | customer405@example.com
 519 | Customer-519 | customer519@example.com
 712 | Customer-712 | customer712@example.com
 791 | Customer-791 | customer791@example.com
 855 | Customer-855 | customer855@example.com
 933 | Customer-933 | customer933@example.com
 970 | Customer-970 | customer970@example.com
(9 rows)

Combining random() with other functions

You can use random() in combination with other functions to generate more complex random data. For example, let's create a table of random events with timestamps within the last 24 hours:

CREATE TABLE random_events (
  id SERIAL PRIMARY KEY,
  event_type TEXT,
  severity INTEGER,
  timestamp TIMESTAMP
);

INSERT INTO random_events (event_type, severity, timestamp)
SELECT
  (ARRAY['Error', 'Warning', 'Info'])[random(1, 3)],
  random(1, 5),
  NOW() - (random() * INTERVAL '24 hours')
FROM generate_series(1, 100);

SELECT * FROM random_events
ORDER BY timestamp DESC
LIMIT 4;

This creates 100 random events with different types, severities, and timestamps within the last 24 hours.

id | event_type | severity |         timestamp
----+------------+----------+----------------------------
 10 | Error	     |        1 | 2024-12-04 09:44:39.651498
 47	| Info	     |        1 | 2024-12-04 09:41:50.372958
 88 | Info	     |        3 | 2024-12-04 09:40:21.689072
 74 | Warning    |        2 | 2024-12-04 09:05:22.546381
(4 rows)

Additional considerations

Seed for reproducibility

The Postgres random() function uses a seed that is initialized at the start of each database session. If you need reproducible random numbers across sessions, you can set the seed manually using the setseed() function:

SELECT setseed(0.3);
SELECT random();

This will produce the same sequence of random numbers in any session where you set the same seed. The setseed() function takes a value between 0 and 1 as its argument.

Performance implications

The random() function is generally fast, but excessive use in large datasets or complex queries can impact performance. For high-performance requirements, consider generating random values in application code or using materialized views with pre-generated random data.

Alternative functions

gen_random_uuid(): Generates a random UUID, useful when you need unique identifiers.