How do you create a random string that's suitable for a session ID in PostgreSQL?

user_70926 0
Viewed 122926

I'd like to make a random string for use in session verification using PostgreSQL. I know I can get a random number with SELECT random(), so I tried SELECT md5(random()), but that doesn't work. How can I do this?

random postgresql
13 Answers

You can fix your initial attempt like this:

SELECT md5(random()::text);

Much simpler than some of the other suggestions. :-)

user_985300

I was playing with PostgreSQL recently, and I think I've found a little better solution, using only built-in PostgreSQL methods - no pl/pgsql. The only limitation is it currently generates only UPCASE strings, or numbers, or lower case strings.

template1=> SELECT array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer) FROM generate_series(1,12)), '');
 array_to_string
-----------------
 TFBEGODDVTDM

template1=> SELECT array_to_string(ARRAY(SELECT chr((48 + round(random() * 9)) :: integer) FROM generate_series(1,12)), '');
 array_to_string
-----------------
 868778103681

The second argument to the generate_series method dictates the length of the string.

user_1902340

select * from md5(to_char(random(), '0.9999999999999999'));

user_5164870 
create extension if not exists pgcrypto;

then

SELECT encode(gen_random_bytes(20),'base64')

or even

SELECT encode(gen_random_bytes(20),'hex')

This is for 20 bytes = 160 bits of randomness (as long as sha1 for example).

user_4337870
Related