Random Data
Explore how to generate random data in PostgreSQL for testing and development purposes. Understand using functions such as random, floor, ceil, and round to produce integers and floats within ranges. Learn how to select random values from arrays and how to create reproducible random data with setseed to ensure consistent testing outcomes.
Generating random data is useful for various purposes, but more often than not, we want to generate random data for testing, development, and benchmarking. For example, you might want to populate data in your local development environment, or you might want to test some query optimization techniques and you need a big table.
The RANDOM function
To generate random numbers PostgreSQL provides a random function. The function then returns a value between 0 and 1:
Note: Try executing this function multiple times, and each time you’ll get a distinct random number between 0 and 1.
Generating random integers
As we’ve seen in the previous example, the random function produces a random number greater than or equal to 0 and less than 1. It does not accept any arguments that allow us to control the range. What should we do if we want a random integer?
To generate values at different ranges, we can use random in an expression. For example, to produce a random float between 0 and 99, we can multiply the result of random() by 100:
Note: ...