Created
March 17, 2024 20:48
-
-
Save cbaatz/e0e36941582ca3ea4d418e4532ccc8d7 to your computer and use it in GitHub Desktop.
PostgreSQL function to generate a random ID using [0-9A-Za-z] with a fixed prefix.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment