Postgres random() function
Generate random values between 0 and 1
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 precisionIt 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.