Random

Another tip from Haki Benita. While it is possible to do this in say Python, it can be useful to do everything in SQL.

Random

To generate random numbers PostgreSQL provides a random function that returns a value between 0 and 1:

1
2

SELECT random();
0.5917508391168769

To generate values at different ranges you can random in an expression:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Random float between 0 and 100
SELECT random() * 100;
59.17508391168769

-- Random integer between 1 and 100
SELECT ceil(random() * 100);
59

-- Random integer between 11 and 100
SELECT 10 + ceil(random() * 90);
59

It’s a common mistake to use round instead of ceil or floor to generate a range of integers. Using round may produce inconsistent distribution. Consider the following query to generate random integers in the range 0 - 4 using round instead ceil:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT
round(random() * 3) AS n,
count(*)
FROM
generate_series(0, 1000)
GROUP BY
1;

n │ count
──┼───────
0 │   150
1 │   328
2 │   341
3 │   182

Notice how the values 0 and 3 are coming up less than 1 and 2. Using round, random values less than 0.5 will be rounded down to 0, and random numbers greater than 2.5 will be rounded up to 3, while for example, random values between 0.5 and 1.5 will be rounded to 1. This makes the edges less likely to come up.

This problem can be solved by either rounding up or down. Consider the same query using ceil:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT
ceil(random() * 3) AS n,
count(*)
FROM
generate_series(0, 1000)
GROUP BY
1;

n │ count
──┼───────
1 │   328
2 │   339
3 │   334

Using ceil produces more evenly distributed random numbers.

Random Choice

You can use the random function to pick a random value from a list of values:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
(array['red', 'green', 'blue'])[ceil(random() * 3)] AS color
FROM
generate_series(1, 5);

color
───────
green
green
blue
green
blue

The expression defines an array of colors, and then uses random to get a random element from the array. Notice that in PostgreSQL, arrays start at 1:

1
2
3
4
5
6

    -- In PostgreSQL arrays start at 1
    SELECT (array['red', 'green', 'blue'])[1];

     array
    ───────
     red