Often in PostgreSQL you may want to update each row in a column with a random value.
You can use the random() function with the following syntax to do so:
UPDATE athletes SET points = floor(random() * (10 + 1));
This particular example updates each value in the points column of the athletes table to have a random number between 0 and 10, inclusive.
Note: To change the upper limit to a different number, simply change the 10 to a different number.
The following example shows how to use this syntax in practice.
Example: How to Update Each Row with Random Value 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, 'Hawks', 10);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Hawks', 20);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points
-----------+---------+--------
1 | Mavs | 22
2 | Hawks | 30
3 | Kings | 12
4 | Kings | 25
5 | Mavs | 31
6 | Mavs | 16
7 | Hawks | 10
8 | Celtics | 14
9 | Hawks | 20
10 | Hawks | 26
Suppose that we would like to update each value in the points column to have a random number between 0 and 10.
We can use the following syntax to do so:
-- update each value in points column to random integer between 0 and 10
UPDATE athletes SET points = floor(random() * (10 + 1));
-- view updated rows in table
SELECT * FROM athletes;
athleteid | team | points
-----------+---------+--------
1 | Mavs | 9
2 | Hawks | 9
3 | Kings | 6
4 | Kings | 5
5 | Mavs | 0
6 | Mavs | 6
7 | Hawks | 7
8 | Celtics | 10
9 | Hawks | 10
10 | Hawks | 2
Notice that each row in the points column now contains random values between 0 and 10, inclusive.
Feel free to use similar syntax to update rows with random numbers 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 Select Random Value from Array
PostgreSQL: How to Count Occurrences of Each Value in Column
