PostgreSQL function to generate a random ID using [0-9A-Za-z] with a fixed prefix.

gen_prefixed_id.sql

-- SELECT gen_prefixed_id('account_', 20);
-- account_9f2oOuK23Xpv3rdtHnQY

-- Example usage in a table column definition:
-- id text DEFAULT gen_prefixed_id('account_', 16) PRIMARY KEY

-- Load the built-in pgcrypto module if not loaded.
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Create a function that takes a text prefix and the length of the
-- random string to attach to the prefix.
CREATE OR REPLACE FUNCTION gen_prefixed_id(prefix text, length int) RETURNS text AS $$
BEGIN
  -- Generate more random bytes than we need. We generate at least 10 bytes
  -- and then twice as many as we want characters. We do that because we
  -- will strip out '+' and '/' later and want to ensure we have a long
  -- enough string afterwards. For longer lengths, generating length bytes
  -- will usually be sufficient (base64 encoding needs 4/3 characters per
  -- byte).
  
  -- We then encode as base64 and strip out '/' and '+' so that the alphabet
  -- is [a-zA-Z0-9]. (This is the same alphabet as base62, but not a base62
  -- encoding. It's a poor-man's work around for the fact that encode()
  -- doesn't support base62.)
  
  -- We then truncate the encoded, stripped string to the desired length.

  -- Finally, we attach the desired prefix.
  
  RETURN concat(prefix, left(translate(encode(gen_random_bytes(length*2+10), 'base64'), '/+', ''), length));
END;
$$ LANGUAGE plpgsql VOLATILE;