By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE OR REPLACE FUNCTION parse_ulid(ulid text) RETURNS bytea AS $$
DECLARE
-- 16byte
bytes bytea = E'\\x00000000 00000000 00000000 00000000';
v char[];
-- Allow for O(1) lookup of index values
dec integer[] = ARRAY[
255, 255, 255, 255, 255, 255, 255, 255, 255, 255,
255, 255, 255, 255, 255, 255, 255, 255, 255, 255,
255, 255, 255, 255, 255, 255, 255, 255, 255, 255,
255, 255, 255, 255, 255, 255, 255, 255, 255, 255,
255, 255, 255, 255, 255, 255, 255, 0, 1, 2,
3, 4, 5, 6, 7, 8, 9, 255, 255, 255,
255, 255, 255, 255, 10, 11, 12, 13, 14, 15,
16, 17, 1, 18, 19, 1, 20, 21, 0, 22,
23, 24, 25, 26, 255, 27, 28, 29, 30, 31,
255, 255, 255, 255, 255, 255, 10, 11, 12, 13,
14, 15, 16, 17, 1, 18, 19, 1, 20, 21,
0, 22, 23, 24, 25, 26, 255, 27, 28, 29,
30, 31
];
BEGIN
IF NOT ulid ~* '^[0-7][0-9ABCDEFGHJKMNPQRSTVWXYZ]{25}$' THEN
RAISE EXCEPTION 'Invalid ULID: %', ulid;
END IF;
v = regexp_split_to_array(ulid, '');
-- 6 bytes timestamp (48 bits)
bytes = SET_BYTE(bytes, 0, (dec[ASCII(v[1])] << 5) | dec[ASCII(v[2])]);
bytes = SET_BYTE(bytes, 1, (dec[ASCII(v[3])] << 3) | (dec[ASCII(v[4])] >> 2));
bytes = SET_BYTE(bytes, 2, (dec[ASCII(v[4])] << 6) | (dec[ASCII(v[5])] << 1) | (dec[ASCII(v[6])] >> 4));
bytes = SET_BYTE(bytes, 3, (dec[ASCII(v[6])] << 4) | (dec[ASCII(v[7])] >> 1));
bytes = SET_BYTE(bytes, 4, (dec[ASCII(v[7])] << 7) | (dec[ASCII(v[8])] << 2) | (dec[ASCII(v[9])] >> 3));
bytes = SET_BYTE(bytes, 5, (dec[ASCII(v[9])] << 5) | dec[ASCII(v[10])]);
CREATE FUNCTION
SELECT ulid_to_uuid('01FGB414J8PPBVHBMHGSXGS21C')
| ulid_to_uuid |
|---|
| 017c1640-9248-b597-b8ae-91867b0c882c |
SELECT 1
SELECT ulid_to_uuid('0123456789ABDEKMNPQRTVWXYZ') AS uppercase,
ulid_to_uuid('0123456789abdekmnpqrtvwxyz') AS lowercase
| uppercase | lowercase |
|---|---|
| 0110c853-1d09-52da-e9d2-b6be35be77df | 0110c853-1d09-52da-e9d2-b6be35be77df |
SELECT 1
SELECT uuid_to_ulid('017c1640-9248-b597-b8ae-91867b0c882c'),
uuid_to_ulid('0110c853-1d09-52da-e9d2-b6be35be77df')
| uuid_to_ulid | uuid_to_ulid |
|---|---|
| 01FGB414J8PPBVHBMHGSXGS21C | 0123456789ABDEKMNPQRTVWXYZ |
SELECT 1
-- Invalid ULID
SELECT ulid_to_uuid('81FGB414J8PPBVHBMHGSXGS21C')
ERROR: Invalid ULID: 81FGB414J8PPBVHBMHGSXGS21C CONTEXT: PL/pgSQL function parse_ulid(text) line 24 at RAISE PL/pgSQL function ulid_to_uuid(text) line 3 at RETURN