Generate random String in PostgreSQL
24,815
Solution 1
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));
Solution 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.
Solution 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
Solution 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)),
'');
Solution 5
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
Author by
Peter Penzov
Updated on July 09, 2022Comments
-
Peter Penzov almost 2 years
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?