Skip to content

Instantly share code, notes, and snippets.

@tsujio

tsujio/postgresql-ulid.sql

Created May 23, 2024 12:19
Show Gist options
  • Save tsujio/379efe19f5fe7696b8fad944ca6ee9e2 to your computer and use it in GitHub Desktop.
Save tsujio/379efe19f5fe7696b8fad944ca6ee9e2 to your computer and use it in GitHub Desktop.
Download ZIP
ULID encode/decode udfs for PostgreSQL
Raw
postgresql-ulid.sql
CREATE OR REPLACE FUNCTION ULID_ENCODE(IN value BYTEA)
RETURNS VARCHAR(26)
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $$
DECLARE
chars CHAR(1)[] := ARRAY['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'J', 'K', 'M', 'N', 'P', 'Q', 'R', 'S', 'T', 'V', 'W', 'X', 'Y', 'Z'];
BEGIN
RETURN CONCAT(
/* Timestamp */
chars[((GET_BYTE(value, 0) & 224) >> 5) + 1],
chars[(GET_BYTE(value, 0) & 31) + 1],
chars[((GET_BYTE(value, 1) & 248) >> 3) + 1],
chars[(((GET_BYTE(value, 1) & 7) << 2) | ((GET_BYTE(value, 2) & 192) >> 6)) + 1],
chars[(((GET_BYTE(value, 2) & 62) >> 1)) + 1],
chars[(((GET_BYTE(value, 2) & 1) << 4) | ((GET_BYTE(value, 3) & 240) >> 4)) + 1],
chars[(((GET_BYTE(value, 3) & 15) << 1) | ((GET_BYTE(value, 4) & 128) >> 7)) + 1],
chars[((GET_BYTE(value, 4) & 124) >> 2) + 1],
chars[(((GET_BYTE(value, 4) & 3) << 3) | ((GET_BYTE(value, 5) & 224) >> 5)) + 1],
chars[(GET_BYTE(value, 5) & 31) + 1],
/* Randomness */
chars[((GET_BYTE(value, 6) & 248) >> 3) + 1],
chars[(((GET_BYTE(value, 6) & 7) << 2) | ((GET_BYTE(value, 7) & 192) >> 6)) + 1],
chars[((GET_BYTE(value, 7) & 62) >> 1) + 1],
chars[(((GET_BYTE(value, 7) & 1) << 4) | ((GET_BYTE(value, 8) & 240) >> 4)) + 1],
chars[(((GET_BYTE(value, 8) & 15) << 1) | ((GET_BYTE(value, 9) & 128) >> 7)) + 1],
chars[((GET_BYTE(value, 9) & 124) >> 2) + 1],
chars[(((GET_BYTE(value, 9) & 3) << 3) | ((GET_BYTE(value, 10) & 224) >> 5)) + 1],
chars[(GET_BYTE(value, 10) & 31) + 1],
chars[((GET_BYTE(value, 11) & 248) >> 3) + 1],
chars[(((GET_BYTE(value, 11) & 7) << 2) | ((GET_BYTE(value, 12) & 192) >> 6)) + 1],
chars[((GET_BYTE(value, 12) & 62) >> 1) + 1],
chars[(((GET_BYTE(value, 12) & 1) << 4) | ((GET_BYTE(value, 13) & 240) >> 4)) + 1],
chars[(((GET_BYTE(value, 13) & 15) << 1) | ((GET_BYTE(value, 14) & 128) >> 7)) + 1],
chars[((GET_BYTE(value, 14) & 124) >> 2) + 1],
chars[(((GET_BYTE(value, 14) & 3) << 3) | ((GET_BYTE(value, 15) & 224) >> 5)) + 1],
chars[(GET_BYTE(value, 15) & 31) + 1]
);
END;
$$
;
CREATE OR REPLACE FUNCTION ULID_DECODE(IN value VARCHAR(26))
RETURNS BYTEA
LANGUAGE plpgsql
IMMUTABLE
STRICT
AS $$
DECLARE
bytes SMALLINT[] := ARRAY[
0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff,
0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff,
0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff,
0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff,
0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff,
0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff,
0x00, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07,
0x08, 0x09, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff,
0xff, 0x0a, 0x0b, 0x0c, 0x0d, 0x0e, 0x0f, 0x10,
0x11, 0xff, 0x12, 0x13, 0xff, 0x14, 0x15, 0xff,
0x16, 0x17, 0x18, 0x19, 0x1a, 0xff, 0x1b, 0x1c,
0x1d, 0x1e, 0x1f, 0xff, 0xff, 0xff, 0xff, 0xff,
0xff, 0x0a, 0x0b, 0x0c, 0x0d, 0x0e, 0x0f, 0x10,
0x11, 0xff, 0x12, 0x13, 0xff, 0x14, 0x15, 0xff,
0x16, 0x17, 0x18, 0x19, 0x1a, 0xff, 0x1b, 0x1c,
0x1d, 0x1e, 0x1f, 0xff, 0xff, 0xff, 0xff, 0xff
];
BEGIN
RETURN
/* Timestamp */
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 1, 1)) & 127) + 1] << 5) | bytes[(ASCII(SUBSTR(value, 2, 1)) & 127) + 1]) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 3, 1)) & 127) + 1] << 3) | (bytes[(ASCII(SUBSTR(value, 4, 1)) & 127) + 1] >> 2)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 4, 1)) & 127) + 1] << 6) | (bytes[(ASCII(SUBSTR(value, 5, 1)) & 127) + 1] << 1) | (bytes[(ASCII(SUBSTR(value, 6, 1)) & 127) + 1] >> 4)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 6, 1)) & 127) + 1] << 4) | (bytes[(ASCII(SUBSTR(value, 7, 1)) & 127) + 1] >> 1)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 7, 1)) & 127) + 1] << 7) | (bytes[(ASCII(SUBSTR(value, 8, 1)) & 127) + 1] << 2) | (bytes[(ASCII(SUBSTR(value, 9, 1)) & 127) + 1] >> 3)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 9, 1)) & 127) + 1] << 5) | bytes[(ASCII(SUBSTR(value, 10, 1)) & 127) + 1]) & 0xff) ||
/* Randomness */
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 11, 1)) & 127) + 1] << 3) | (bytes[(ASCII(SUBSTR(value, 12, 1)) & 127) + 1] >> 2)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 12, 1)) & 127) + 1] << 6) | (bytes[(ASCII(SUBSTR(value, 13, 1)) & 127) + 1] << 1) | (bytes[(ASCII(SUBSTR(value, 14, 1)) & 127) + 1] >> 4)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 14, 1)) & 127) + 1] << 4) | (bytes[(ASCII(SUBSTR(value, 15, 1)) & 127) + 1] >> 1)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 15, 1)) & 127) + 1] << 7) | (bytes[(ASCII(SUBSTR(value, 16, 1)) & 127) + 1] << 2) | (bytes[(ASCII(SUBSTR(value, 17, 1)) & 127) + 1] >> 3)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 17, 1)) & 127) + 1] << 5) | bytes[(ASCII(SUBSTR(value, 18, 1)) & 127) + 1]) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 19, 1)) & 127) + 1] << 3) | (bytes[(ASCII(SUBSTR(value, 20, 1)) & 127) + 1] >> 2)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 20, 1)) & 127) + 1] << 6) | (bytes[(ASCII(SUBSTR(value, 21, 1)) & 127) + 1] << 1) | (bytes[(ASCII(SUBSTR(value, 22, 1)) & 127) + 1] >> 4)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 22, 1)) & 127) + 1] << 4) | (bytes[(ASCII(SUBSTR(value, 23, 1)) & 127) + 1] >> 1)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 23, 1)) & 127) + 1] << 7) | (bytes[(ASCII(SUBSTR(value, 24, 1)) & 127) + 1] << 2) | (bytes[(ASCII(SUBSTR(value, 25, 1)) & 127) + 1] >> 3)) & 0xff) ||
SET_BYTE(E'\\x00', 0, ((bytes[(ASCII(SUBSTR(value, 25, 1)) & 127) + 1] << 5) | bytes[(ASCII(SUBSTR(value, 26, 1)) & 127) + 1]) & 0xff)
;
END;
$$
;
/*
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8B0W84WTGTBE3P3GZ')) = '01HYJNZDH8B0W84WTGTBE3P3GZ';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8CBZPP3GP1YXD2WSP')) = '01HYJNZDH8CBZPP3GP1YXD2WSP';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8JQEZX5SE8TB6P5GA')) = '01HYJNZDH8JQEZX5SE8TB6P5GA';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8SNWRRKB7B9ZECZBR')) = '01HYJNZDH8SNWRRKB7B9ZECZBR';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH892YCT47AJ3N9YKJ1')) = '01HYJNZDH892YCT47AJ3N9YKJ1';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8MRAP80TMXTR27BFJ')) = '01HYJNZDH8MRAP80TMXTR27BFJ';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8ST5SAAN8B8SR5XKR')) = '01HYJNZDH8ST5SAAN8B8SR5XKR';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8NHHFJMEDYRF8H215')) = '01HYJNZDH8NHHFJMEDYRF8H215';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8WJ1QSYESB8WMGQYP')) = '01HYJNZDH8WJ1QSYESB8WMGQYP';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8NHC7SGA36RJX4M46')) = '01HYJNZDH8NHC7SGA36RJX4M46';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8QC7HJR5WA98HKJAR')) = '01HYJNZDH8QC7HJR5WA98HKJAR';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8FEV9S3YY2K92TT25')) = '01HYJNZDH8FEV9S3YY2K92TT25';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8C85S5SS4SRENKDB4')) = '01HYJNZDH8C85S5SS4SRENKDB4';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH831WWSC7DY367FE0R')) = '01HYJNZDH831WWSC7DY367FE0R';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH803YGY8MRHHDMKFH8')) = '01HYJNZDH803YGY8MRHHDMKFH8';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8AGCNK1QYKB78B142')) = '01HYJNZDH8AGCNK1QYKB78B142';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH82DZ011V2D04P6JY2')) = '01HYJNZDH82DZ011V2D04P6JY2';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8FEMX5FKX6B6MC3YG')) = '01HYJNZDH8FEMX5FKX6B6MC3YG';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8RQZKHGN0V3KKFH6N')) = '01HYJNZDH8RQZKHGN0V3KKFH6N';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH83DS1PS4TD4B7HDS0')) = '01HYJNZDH83DS1PS4TD4B7HDS0';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH82E9T04NK8C8PGN0G')) = '01HYJNZDH82E9T04NK8C8PGN0G';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH89X97JXYG089EW71B')) = '01HYJNZDH89X97JXYG089EW71B';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH82B19W05ZQW6HB1WY')) = '01HYJNZDH82B19W05ZQW6HB1WY';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8YA3G164VGZ2MVW08')) = '01HYJNZDH8YA3G164VGZ2MVW08';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8H38JYTVEYVX800F8')) = '01HYJNZDH8H38JYTVEYVX800F8';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH83ATHR2BQGN3Z0BFN')) = '01HYJNZDH83ATHR2BQGN3Z0BFN';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8YVR31J9NP5ZAVB02')) = '01HYJNZDH8YVR31J9NP5ZAVB02';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8HZVM6QVD6YF3KFZH')) = '01HYJNZDH8HZVM6QVD6YF3KFZH';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8YPDNG0JA29JEZY2Z')) = '01HYJNZDH8YPDNG0JA29JEZY2Z';
SELECT ULID_ENCODE(ULID_DECODE('01HYJNZDH8W7612TBF1HCBKH5K')) = '01HYJNZDH8W7612TBF1HCBKH5K';
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment