You often need to generate random strings, like for login cookies and unique entry codes.
These strings always need to be stored in the database.So make your life simpler by generating the random strings directly in the database.Here's a very handy PostgreSQL function to do it:
create function gen_random_bytes(int) returns bytea as'$libdir/pgcrypto', 'pg_random_bytes' language c strict;create function random_string(len int) returns text as $$declare chars text[] = '{0,1,2,3,4,5,6,7,8,9,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}'; result text = ''; i int = 0; rand bytea;begin -- generate secure random bytes and convert them to a string of chars. rand = gen_random_bytes($1); for i in 0..len-1 loop -- rand indexing is zero-based, chars is 1-based. result = result || chars[1 + (get_byte(rand, i) % array_length(chars, 1))]; end loop; return result;end;$$ language plpgsql;Give it a number: the length of the random string you want.It will return random alphanumeric text of that length.
select random_string(8);random_string ─────────────── yBuXga02select random_string(8); random_string ─────────────── eP3X7yqeThe chance of random clash is very small.But you need to be completely sure that the new random string is unique — not already existing in that column in that table.So here's a function where you give it the string length, table name, and column name.It will return a random string confirmed to be unique — to not exist there already.It gets a random string, searches for it in that table and column, and if not found, returns it.Otherwise, if it is found, gets a new random string and loops back, trying again until not found.
-- return random string confirmed to not exist in given tablename.colnamecreate function unique_random(len int, _table text, _col text) returns text as $$declare result text; numrows int;begin result = random_string(len); loop execute format('select 1 from %I where %I = %L', _table, _col, result); get diagnostics numrows = row_count; if numrows = 0 then return result; end if; result = random_string(len); end loop;end;$$ language plpgsql;I used to call functions like this using database triggers, called on any insert.But then I found out something surprisingly cool and so much simpler:You can call functions as default values directly in table definitions.
Look at this table, calling unique_random as its default value:
create table things ( code char(8) primary key default unique_random(8, 'things', 'code'), name text);So simple and clear!To use it, you just do a regular insert, and it generates the guaranteed-unique default value.
insert into things (name) values ('one') returning *; code │ name ──────────┼────── nRSXbVWQ │ oneinsert into things (name) values ('two') returning *; code │ name ──────────┼────── EAS9wGcl │ twoI've found this particularly handy for creating login cookies:
create table cookies ( person_id int primary key, cookie char(32) unique default unique_random(32, 'cookies', 'cookie'));Instead of having your client code, your JavaScript, Python, Ruby or whatever, generating the random code, it's extra-nice to have this in your database directly, not only because it's cleaner, but because it saves repeated calls between your client code and database, confirming uniqueness.One simple insert of the person_id returns the unique and already-saved random cookie string:
insert into cookies (person_id) values (1) returning *; person_id │ cookie ───────────┼────────────────────────────────── 1 │ 0P8Tp4wjXuTqCCh1NCR9XIom20z9IcYvDownload the code at /code/rand1.sql.
