Generate random String in PostgreSQL

Question

I'm using this SQL query to generate random value in PostgreSQL

chr(ascii('B') + (random() * 25)::integer)

How I can generate 15 characters random String using the same query?

Machiavelli

How about concatenating it together 15 times? – Swinney

@GordonLinoff: Never underestimate the power of brute force. – Sherlocke

Can you give some example? – Machiavelli

Answer 1

Another solution that's pretty easy to read (perf should be reasonable, but no benchmarks were performed):

select substr(md5(random()::text), 1, 25);

Could be uppercased if you prefer:

select upper(substr(md5(random()::text), 1, 25));

Deledda

Please note the behavior of PostgreSQL's substr. If you want to get the first 25 characters, please use select substr(md5(random()::text), 1, 25), the first parameter from of substr refers to the character from starts and is calculated from 1. If you specify 0, the final result of substr will be one less character, because there is no character in the 0 position, it will assume you have it and return nothing. – Seraphina

Answer 2

Here is my contrib

postgres=# SELECT array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) from generate_series(1,50)),'');
                  array_to_string                   
----------------------------------------------------
 4XOS6TQG5JORLF3D1RPXUWR2FQKON9HIXV0UGH0CQFT1LN5D4L
(1 row)

It lets you specify the set of allowed characters and the length of the string.

Mccowyn

(random()*(36-1)+1)::int will round a random number between 1 and 36 to the nearest integer. This means that the frequency of the first and last characters is about half that of the rest. You can use floor(random()*36)::int+1 instead. – Ancell

Answer 3

This will give you a random word of length 15 consisting of the letters configured in the source values constant

select
  string_agg(substr(characters, (random() * length(characters) + 1)::integer, 1), '') as random_word
from (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')) as symbols(characters)
  -- length of word
  join generate_series(1, 15) on 1 = 1

EDIT: to obtain multiple random words you can use the following:

with symbols(characters) as (VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'))
select string_agg(substr(characters, (random() * length(characters) + 1) :: INTEGER, 1), '')
from symbols
join generate_series(1,8) as word(chr_idx) on 1 = 1 -- word length
join generate_series(1,10000) as words(idx) on 1 = 1 -- # of words
group by idx

Strangles

This solution can be highly performant when updating an existing dataset too, really like it – Corneous

select min(n), max(n) from (select (random() * length('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') + 1)::int n from generate_series(1, 10000000)) t0 returns values 1 to 63, inclusive. That means the call to substr may return an empty string, which will cause the returned value to be less than 8 characters. Just a caveat, good solution nonetheless. – Trident

Answer 4

Yes can do this by single query also but if you want every char should be separate according to range then above is solution

SELECT array_to_string(ARRAY(
            SELECT chr((ascii('B') + round(random() * 25)) :: integer) 
            FROM generate_series(1,15)), 
             '');

Thiamine

Answer 5

I use this, for generating random strings...

If you dont mind dashes and have the uuid extension enabled...

select substr(uuid_generate_v4()::text,1,15);

e.g. to generate a random string in the name column, i will use

select concat('name-', substr(uuid_generate_v4()::text,1,10)) as name;

e.g. name-91fc72dc-d

else, use the excellent md5 example from @fncomp

nb: To enable the uuid extension

create extension if not exists "uuid-ossp";

Chafee

Or you use gen_random_uuid() which is core since Postgres 13+, no extension needed, making this for example SELECT substr(gen_random_uuid()::text, 1, 8). – Erubescence

Answer 6

For me the most convenient way is to create a function:

CREATE OR REPLACE FUNCTION random_string(int) RETURNS TEXT as $$
SELECT substr(md5(random()::text), 0, $1+1);                 
$$ language sql;  

The function is named random_string
and It takes string length as an argument.

And then I use this function anywhere I want

Just see the result:

select random_string(6);

Make a single insert:

insert into users values(nextval('users_sequence'), random_string(6), random_string(6));

Generate multiple rows with random data:

do $$
begin
for i in 1..100 loop
insert into users values(nextval('users_sequence'), random_string(6), random_string(6));
end loop;
end;
$$;

and so on.

Effieeffigy

Answer 7

I tried to use solution from @Bennit but noticed some flaws. The random part is calculated a bit wrongly, that leads wrong results: the resulting lenght is differs (shorter) than desired.

[took a quick look at the @lyndon-s version - most probably it also has the same drawback]

So here is updated version of @bennit version:

select
string_agg(substr(characters, (random() * length(characters) + 0.5)::integer, 1), '') as random_word
from (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')) as symbols(characters)
-- length of word
join generate_series(1, 15) on 1 = 1

And here is demonstration why the change is required:

Corrected:

select n,count(*) from (
select (random() * 10 + 0.5)::integer as n from dbfiles
join generate_series(1, 100000) on 1 = 1
) as s group by n
order by 1;

Original:

select n,count(*) from (
select (random() * 10 + 1)::integer as n from dbfiles
join generate_series(1, 100000) on 1 = 1
) as s group by n
order by 1;

Alumna

Answer 8

Here is the idea:

select (chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) 
       ) as Random15

Swinney

Is there any simple solution? – Machiavelli

Answer 9

Here's my PL/pgSQL take:

• there is an option to generate texts in english or russian symbols, and it's easy to expand for more languages;
• optional usage of digits, punctuation symbols, whitespaces and linebreaks;

create or replace function random_string (
    str_length integer, lang varchar(2) default 'en', 
    w_dig boolean default true, w_punct boolean default true, 
    w_space boolean default false, w_newline boolean default false
)

returns text
language plpgsql
as $function$
     declare
         chars_eng text[] := '{A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
         chars_rus text[] := '{А,Б,В,Г,Д,Е,Ё,Ж,З,И,Й,К,Л,М,Н,О,П,Р,С,Т,У,Ф,Х,Ц,Ч,Ш,Щ,Ъ,Ы,Ь,Э,Ю,Я,а,б,в,г,д,е,ё,ж,з,и,й,к,л,м,н,о,п,р,с,т,у,ф,х,ц,ч,ш,щ,ъ,ы,ь,э,ю,я}';
         chars_dig text[] := '{}';
         chars_punct text[] := '{}';
         chars_space text[] := '{}';
         chars_newline text[] := '{}';
         chars_final text[] := '{}';
         result text := '';
         i integer := 0;
    begin

        -- checking string length arg
        if str_length < 0 then
            raise exception 'Length of string cannot be a negative value';
        end if;

        -- checking chars selection
        if w_dig = true then
            chars_dig := '{0,1,2,3,4,5,6,7,8,9}';
        end if;
        if w_punct = true then
            chars_punct := string_to_array(E'!d"d#d$d%d&d\'d(d)d*d+d,d-d.d/d:d;d<d=d>d?d@d[d\\d]d^d_d`d{d|d}d~','d');
        end if;
        if w_space = true then
            chars_space := string_to_array(' ',',');
        end if;
        if w_newline = true then
            chars_newline := string_to_array(E'\r\n',',');
        end if;

        -- checking language selection
        if lang = 'en' then
            chars_final := chars_eng||chars_dig||chars_punct||chars_space||chars_newline;
        elsif lang = 'ru' then
            chars_final := chars_rus||chars_dig||chars_punct||chars_space||chars_newline;
        else 
            raise exception 'Characters set for that language is not defined';
        end if;

        -- filling the string
        for i in 1..str_length loop
            result := result || chars_final[1 + round(random() * (array_length(chars_final, 1) - 1))];
        end loop;

        -- trimming extra symbols that may appear from /r/n usage
        if length(result) > str_length then
            result := left(result, str_length);
        end if;

        -- getting the result
        return result;

    end;
$function$ ;

Lacylad

Answer 10

Throwing in my 2c here. I needed random strings to do some benchmarks, so all that really mattered to me was that the strings were unique from each other.

select rpad(generate_series::varchar, 1000, 'hi') from generate_series(1,10);

rpad - pad right till length(1000), padded with 'hi' generate_series(1,10) - generate 10 rows

Combining with an answer above, you could also do this:

select rpad(generate_series::varchar, 1000, md5(random()::text)) from generate_series(1,10)

That makes sure you have 200 chars, or whatever the desired length is.

Millner

Answer 11

Well how about a recursive CTE. Combine with generate series to get however many you want.

with recursive brs(rstg, n) as 
     ( select chr(ascii('B') + (random() * 25)::integer), 1
         from generate_series(1,50)    --- or however many you want
       union all 
       select rstg || chr(ascii('B') + (random() * 25)::integer), n+1
         from brs 
        where n<= 15 
     )
select rstg 
  from brs
 where n=15;

Unwept

Answer 12

With orafce extension installed, you have an handy dbms_random.string()

Example for 15 characters from A to Z:

postgres=# create extension orafce;
CREATE EXTENSION
postgres=# select dbms_random.string('U',15);
     string
-----------------
 YCSPRBOFKXPCBQI
(1 row)

More info: https://www.yugabyte.com/blog/generate-random-text-strings-in-postgresql/

Glottology

Answer 13

Random upper case string with in range random length:

UPPER(SUBSTRING(MD5(concat(now(),RANDOM())) FOR RANDOM()*2+5))

Hurdle

Answer 14

Another solution that expands on the original question and contains multiple character sets ([A-Z][a-z][0-9]). This solution is around ~20% faster according to my (non-scientific) measurements than the solution mentioned by @Bennit.

select array_to_string(array(
    select chr(
        case
            when n<10 then ascii('0')+n
            when n<36 then  ascii('a')+(n-10)
            else ascii('A')+(n-36)
        end
    ) from (
        select (random()*61)::int as n from generate_series(0, 20)) x
    ), '') as random_str

Polky

Answer 15

I'm using encode(md5(gen_random_uuid()::text)::bytea,'base64')

For context, here's how I'm generating a base64-encoded secret for JWT token generation for Postgrest:

CREATE OR REPLACE FUNCTION public.exec(sql text)
RETURNS VOID AS 
$BODY$
BEGIN 
 EXECUTE sql; 
END 
$BODY$ LANGUAGE 'plpgsql';

select public.exec('
ALTER DATABASE mydb
SET "app.jwt_secret" 
TO ''' || encode(md5(gen_random_uuid()::text)::bytea,'base64') || ''';');

select current_setting('app.jwt_secret');

Entomophilous

Answer 16

Lots of interesting answers here, I am surprised nobody mentioned pgcrypto as an option

CREATE EXTENSION IF NOT EXISTS pgcrypto;

SELECT encode(gen_random_bytes(64), 'hex') AS random_string;

Not sure of performance compared to some of the homebaked suggestions here

Seabolt