How to Generate Random Strings in PostgreSQL (With Example)

Often in PostgreSQL you may want to generate a random string with a specific length.

The easiest way to do so is by using the md5() function with the following syntax:

SELECT *,
       substr(md5(random()::text), 1, 10) AS random_string
FROM athletes;

This particular example will create a new column in the athletes table named random_string that contains a random string of letters and numbers with a length of 10.

Note: To generate a random string with a different length, simply change the 10 to a different number.

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

Example: How to Generate Random Strings 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 (
  athleteID 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 add a new column that contains a random string for each row with a length of 10.

We can use the following syntax to do so:

SELECT *,
       substr(md5(random()::text), 1, 10) AS random_string
FROM athletes;

 athleteid |  team   | points | random_string 
-----------+---------+--------+---------------
         1 | Mavs    |     22 | 1fe8e7f1f5
         2 | Hawks   |     30 | 5d93573112
         3 | Kings   |     12 | 960839b100
         4 | Kings   |     25 | 9befa263de
         5 | Mavs    |     31 | c16111497c
         6 | Mavs    |     16 | e5f09adced
         7 | Hawks   |     10 | c2971e537e
         8 | Celtics |     14 | deebe2ad52
         9 | Hawks   |     20 | 829e258e23
        10 | Hawks   |     26 | c6ddc48b16

Notice that the resulting table contains a new column named random_string that contains a random string for each row with a length of 10.

You can use similar syntax to generate a random string with a specific length in your own table in PostgreSQL.

Note: You can find the complete documentation for the md5() function in PostgreSQL here.

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 Select Random Value from Array