Skip to content

Instantly share code, notes, and snippets.

@5argon
Last active April 8, 2020 09:50
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save 5argon/027f52c553483aff6b525d7c96d39757 to your computer and use it in GitHub Desktop.
Save 5argon/027f52c553483aff6b525d7c96d39757 to your computer and use it in GitHub Desktop.
Create a PostgreSQL function that generates a random a Base32 string. Note that it might not be compatible with 8-bit ASCII under certain length because it maps each 5 bits to one Base32 character, so multiples of 8 will work. (8 5-bit Base32 characters = 40-bit = 5 8-bit ASCII characters) (Adapted from : http://stackoverflow.com/questions/39707…
CREATE OR REPLACE FUNCTION yourschema.randomBase32(length integer)
RETURNS text AS $$
declare
chars text[] := '{2,3,4,5,6,7,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 integer := 0;
begin
if length < 0 then
raise exception 'Given length cannot be less than 0';
end if;
for i in 1..length loop
result := result || chars[1+random()*(array_length(chars, 1)-1)];
end loop;
return result;
end;
$$
LANGUAGE 'plpgsql' STABLE
@5argon
Copy link
Author

5argon commented Feb 17, 2017

Example usage

yourdb=# SELECT yourschema.randombase32(8);
 randombase32 
--------------
 G5XUN35G
(1 row)

@marton78
Copy link

marton78 commented Apr 8, 2020

This function should be marked VOLATILE rather than STABLE, see e.g. here

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment