Postgres random() function

The Postgres random() function generates random floating point values between 0.0 and 1.0.

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 signature

The random() function has a simple form:

random() -> double precision

It returns a uniformly distributed random value between 0.0 (inclusive) and 1.0 (exclusive).

Example usage

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

To generate random integers within a specific range, we can use the random() function in combination with other operations. Here's an example simulating a dice rolling game where players roll two six-sided dice:

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,
  1 + floor(random() * 6)::INTEGER,  -- Random integer between 1 and 6
  1 + floor(random() * 6)::INTEGER,  -- 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.

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'])[1 + floor(random() * 3)::INTEGER],
  1 + floor(random() * 5)::INTEGER,
  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
----+------------+----------+----------------------------
 26 | Error      |        3 | 2024-06-23 10:33:15.164475
 69 | Warning    |        5 | 2024-06-23 10:29:38.926118
 72 | Warning    |        4 | 2024-06-23 10:13:55.993455
 68 | Warning    |        3 | 2024-06-23 09:56:44.098039
(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.