Often in PostgreSQL you may want to select a random value from an array.
You can use the following syntax to do so:
SELECT *,
(ARRAY['Good', 'Bad', 'OK'])[FLOOR(RANDOM()*3+1)] AS status
FROM athletes;
This particular example will create a new column named status that contains a value randomly chosen from the following three values:
- Good
- Bad
- OK
Note: The number that we multiply the RANDOM() function by should match the number of values in the array.
The following example shows how to use this syntax in practice.
Example: How to Select Random Value from Array 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 randomly assigns each player a status of Good, Bad or OK.
We can use the following syntax to do so:
SELECT *,
(ARRAY['Good', 'Bad', 'OK'])[FLOOR(RANDOM()*3+1)] AS status
FROM athletes;
id | team | points | status
----+---------+--------+--------
1 | Mavs | 22 | OK
2 | Hawks | 30 | Good
3 | Kings | 12 | Bad
4 | Kings | 25 | OK
5 | Mavs | 31 | Good
6 | Mavs | 16 | Bad
7 | Celtics | 40 | OK
8 | Celtics | 14 | Bad
9 | Nuggets | 19 | Bad
10 | Hawks | 26 | OK
Notice that the resulting table contains a new column named status that contains a random value chosen from the potential values of Good, Bad and OK.
Feel free to use similar syntax to select a random value from your own array.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Generate a Random Integer
PostgreSQL: How to Generate Random Dates
PostgreSQL: How to Round Numbers Up to Nearest Integer
