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?

@GordonLinoff: Never underestimate the power of brute force. — Mike Sherrill 'Cat Recall', Apr 10 '16 at 18:01

fncomp · Answer 1 · 2019-01-09T05:57:02.570

48

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

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

Could be uppercased if you prefer:

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

edited Jan 09 '19 at 05:57

answered Jan 09 '19 at 05:50

fncomp

6,040
3
33
42

score 21 · Answer 2 · answered Nov 16 '17 at 03:47

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.

Bennit · Answer 3 · 2017-05-26T11:59:08.767

11

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

edited May 26 '17 at 11:59

answered May 26 '17 at 11:25

Bennit

398
4
11

This solution can be highly performant when updating an existing dataset too, really like it – bobmarksie Oct 23 '20 at 08:10
`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. – François Beausoleil Nov 12 '21 at 17:36

score 6 · Answer 4 · answered Aug 04 '17 at 07:51

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)), 
             '');

score 4 · Answer 5 · answered Mar 24 '22 at 11:08

4

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";

answered Mar 24 '22 at 11:08

mlo55

6,663
6
33
26

2

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)`. – kino1 Sep 14 '22 at 21:31

score 3 · Answer 6 · answered Jul 16 '21 at 21:03

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;

luke · Answer 7 · 2022-09-15T09:55:49.550

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.

score 2 · Answer 8 · answered Apr 10 '16 at 23:09

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

Is there any simple solution? – Peter Penzov Apr 11 '16 at 07:02 — Peter Penzov, Apr 11 '16 at 07:02

NickNal · Answer 9 · 2022-03-31T19:33:36.093

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$ ;

score 0 · Answer 10 · answered Feb 09 '21 at 02:31

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.

score 0 · Answer 11 · answered Feb 10 '21 at 01:29

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;

FranckPachot · Answer 12 · 2023-05-16T05:37:05.497

0

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/

edited May 16 '23 at 05:37

answered May 15 '23 at 13:26

FranckPachot

414
4
10

Generate random String in PostgreSQL

12 Answers12

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

And then I use this `function` anywhere I want

Just see the result:

Make a single insert:

Generate multiple rows with random data:

Linked

Generate random String in PostgreSQL

12 Answers12

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

And then I use this function anywhere I want

Just see the result:

Make a single insert:

Generate multiple rows with random data:

Linked

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

And then I use this `function` anywhere I want