How to Generate a Random Integer in PostgreSQL (With Example)

Often in PostgreSQL you may want to generate a random integer in a range.

You can use the following syntax to do so:

SELECT *,
       floor(random() * (20 + 1)) AS random_int
FROM athletes;

This particular example will create a new column named random_int that contains a random integer between 0 and 20, inclusive.

Note: To change the upper limit to a different number, simply change the 20 to a different number.

The following example shows how to use this syntax in practice.

Example: How to Generate a Random Integer in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 16);
INSERT INTO athletes VALUES (0007, 'Celtics', 40);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Nuggets', 19);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);

-- view all rows in table
SELECT * FROM athletes;

 id |  team   | points 
----+---------+--------
  1 | Mavs    |     22
  2 | Hawks   |     30
  3 | Kings   |     12
  4 | Kings   |     25
  5 | Mavs    |     31
  6 | Mavs    |     16
  7 | Celtics |     40
  8 | Celtics |     14
  9 | Nuggets |     19
 10 | Hawks   |     26

Suppose that we would like to create a new column that contains random integers between 0 and 20.

We can use the following syntax to do so:

SELECT *,
       floor(random() * (20 + 1)) AS random_int
FROM athletes;

 id |  team   | points | random_int 
----+---------+--------+------------
  1 | Mavs    |     22 |         13
  2 | Hawks   |     30 |          2
  3 | Kings   |     12 |          8
  4 | Kings   |     25 |          0
  5 | Mavs    |     31 |         16
  6 | Mavs    |     16 |         17
  7 | Celtics |     40 |         11
  8 | Celtics |     14 |         15
  9 | Nuggets |     19 |         20
 10 | Hawks   |     26 |          4

Notice that the resulting table contains a new column named random_int that contains random integers between 0 and 20.

Note: Using this approach, both 0 and 20 are potential integers that could be randomly generated.

Feel free to use similar syntax to generate random integers in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Numbers from a String
PostgreSQL: How to Pad Numbers with Leading Zeros
PostgreSQL: How to Count Occurrences of Each Value in Column