Generate unique random values directly in the database

2022-03-01

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;

download code

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 
───────────────
 yBuXga02

select random_string(8);
 random_string 
───────────────
 eP3X7yqe

download code

The 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.colname
create 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;

download code

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

download code

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 │ one

insert into things (name) values ('two') returning *;

   code   │ name 
──────────┼──────
 EAS9wGcl │ two

download code

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

download code

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 │ 0P8Tp4wjXuTqCCh1NCR9XIom20z9IcYv

download code

Download the code at /code/rand1.sql.