PostgreSQL implementation of ULID

pg_ulid.sql

-- Inspired by MySQL implementation of ULID by
-- https://gist.github.com/kenji4569/47ce8bbd6bef7b85ba1f97e018f34cf3

-- Add pgcrypto extension
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Decodes a valid ulid (26 digits) into its 128-bit form
-- encoded as 32 character hex
CREATE OR REPLACE FUNCTION ULID_DECODE(s CHAR(26))
    RETURNS CHAR(32)
    LANGUAGE plpgsql
    IMMUTABLE
    STRICT
    PARALLEL SAFE AS $$
DECLARE
    -- Crockford base 32 charset
    cc CHAR(32) DEFAULT '0123456789ABCDEFGHJKMNPQRSTVWXYZ';
    -- 128 bit ulid initialized with all zeros
    sb BIT(128) DEFAULT 0::BIT(128);
    -- base conversion result initialized empty
    rr CHAR(32) DEFAULT '';
    i INT;
BEGIN
    -- allow decoding to be case-insensitive
    s := UPPER(s);

    -- loop through 26 digits of base 32 ulid
    FOR i in 1..26 LOOP
        -- shift bit accummulator left 5 bits and perform a
        -- bitwise OR with coverted base 32 digit (5-bit binary)
        sb := (sb << 5) | (POSITION(SUBSTRING(s, i, 1) IN cc) - 1)::BIT(128);
    END LOOP;

    -- loop through 128-bit ulid
    FOR i in 0..3 LOOP
        -- extract 4 bytes (32-bits) per iteration and
        -- convert to hex via int to hex convertion and
        -- append to result
        rr := rr || LPAD(TO_HEX(SUBSTRING(sb, (i * 32) + 1, 32)::INT), 8, '0');
    END LOOP;

    RETURN rr;
END;
$$;

-- Encode a valid 128-bit ulid from a 32 character hex
-- into its base 32 (26 digit) form
CREATE OR REPLACE FUNCTION ULID_ENCODE(hex CHAR(32))
    RETURNS CHAR(26)
    LANGUAGE plpgsql
    IMMUTABLE
    STRICT
    PARALLEL SAFE AS $$
DECLARE
    -- Crockford base 32 charset
    cc CHAR(32) DEFAULT '0123456789ABCDEFGHJKMNPQRSTVWXYZ';
    -- 128 bit ulid initialized from hex input
    sb BIT(128) DEFAULT ('x' || hex)::BIT(128);
    -- base conversion result initialzed empty
    rr CHAR(26) DEFAULT '';
    i INT;
BEGIN
    -- loop through 128-bit ulid
    FOR i in 1..26 LOOP
        -- extract last 5 bits and use its integer equivalent to 
        -- index a specific chracter from the base 32 charset
        -- and append to result
        rr := SUBSTRING(cc, SUBSTRING(sb, 124, 5)::INT + 1, 1) || rr;
        -- shift ulid 5 bits right
        sb := sb >> 5;
    END LOOP;

    RETURN rr;
END;
$$;

-- Generate a ulid from a given timestamp
CREATE OR REPLACE FUNCTION ULID_FROM_DATETIME(t TIMESTAMP)
    RETURNS CHAR(26)
    LANGUAGE plpgsql
    STRICT
    PARALLEL SAFE  AS $$
BEGIN
    -- generate and encode ulid
    RETURN ULID_ENCODE(
        -- convert timestamp to 48-bit (12 character) hex
        LPAD(TO_HEX((EXTRACT(EPOCH FROM t) * 1000)::BIGINT), 12, '0') ||
        -- generate radom 80-bit (20 character) hex
        ENCODE(GEN_RANDOM_BYTES(10), 'hex')
    );
END;
$$;

-- Extract timestamp from ulid
CREATE OR REPLACE FUNCTION ULID_TO_DATETIME(s CHAR(26))
    RETURNS TIMESTAMP
    LANGUAGE plpgsql
    IMMUTABLE
    STRICT
    PARALLEL SAFE AS $$
BEGIN
    RETURN TO_TIMESTAMP(('x' || LEFT(ULID_DECODE(s), 12))::BIT(48)::BIGINT / 1000);
END;
$$;

-- Generate a ulid using the current timestamp
CREATE OR REPLACE FUNCTION ULID() RETURNS CHAR(26)
    LANGUAGE plpgsql
    PARALLEL SAFE AS $$
BEGIN
    RETURN ULID_FROM_DATETIME(CURRENT_TIMESTAMP);
END;
$$;

---

-- Check the all outputs should equal to TRUE
select '01ARZ3NDEKTSV4RRFFQ69G5FAV' = ULID_ENCODE(ULID_DECODE('01ARZ3NDEKTSV4RRFFQ69G5FAV'));
select '00000000000000000000000000' = ULID_ENCODE(ULID_DECODE('00000000000000000000000000'));
select '7F000000000000000000000000' = ULID_ENCODE(ULID_DECODE('7F000000000000000000000000'));
select '0V000000000000000000000000' = ULID_ENCODE(ULID_DECODE('0V000000000000000000000000'));
select '000V0000000000000000000000' = ULID_ENCODE(ULID_DECODE('000V0000000000000000000000'));
select '0000V000000000000000000000' = ULID_ENCODE(ULID_DECODE('0000V000000000000000000000'));
select '00000V00000000000000000000' = ULID_ENCODE(ULID_DECODE('00000V00000000000000000000'));
select '000000V0000000000000000000' = ULID_ENCODE(ULID_DECODE('000000V0000000000000000000'));
select '0000000V000000000000000000' = ULID_ENCODE(ULID_DECODE('0000000V000000000000000000'));
select '00000000V00000000000000000' = ULID_ENCODE(ULID_DECODE('00000000V00000000000000000'));
select '000000000V0000000000000000' = ULID_ENCODE(ULID_DECODE('000000000V0000000000000000'));
select '0000000000V000000000000000' = ULID_ENCODE(ULID_DECODE('0000000000V000000000000000'));
select '00000000000V00000000000000' = ULID_ENCODE(ULID_DECODE('00000000000V00000000000000'));
select '000000000000V0000000000000' = ULID_ENCODE(ULID_DECODE('000000000000V0000000000000'));
select '0000000000000V000000000000' = ULID_ENCODE(ULID_DECODE('0000000000000V000000000000'));
select '00000000000000V00000000000' = ULID_ENCODE(ULID_DECODE('00000000000000V00000000000'));
select '000000000000000V0000000000' = ULID_ENCODE(ULID_DECODE('000000000000000V0000000000'));
select '0000000000000000V000000000' = ULID_ENCODE(ULID_DECODE('0000000000000000V000000000'));
select '00000000000000000V00000000' = ULID_ENCODE(ULID_DECODE('00000000000000000V00000000'));
select '000000000000000000V0000000' = ULID_ENCODE(ULID_DECODE('000000000000000000V0000000'));
select '0000000000000000000V000000' = ULID_ENCODE(ULID_DECODE('0000000000000000000V000000'));
select '00000000000000000000V00000' = ULID_ENCODE(ULID_DECODE('00000000000000000000V00000'));
select '000000000000000000000V0000' = ULID_ENCODE(ULID_DECODE('000000000000000000000V0000'));
select '0000000000000000000000V000' = ULID_ENCODE(ULID_DECODE('0000000000000000000000V000'));
select '00000000000000000000000V00' = ULID_ENCODE(ULID_DECODE('00000000000000000000000V00'));
select '000000000000000000000000V0' = ULID_ENCODE(ULID_DECODE('000000000000000000000000V0'));
select '0000000000000000000000000V' = ULID_ENCODE(ULID_DECODE('0000000000000000000000000V'));
select '0123456789JKMNPQRSTVWXYZ01' = ULID_ENCODE(ULID_DECODE('0123456789JKMNPQRSTVWXYZ01'));
select '00123456789JKMNPQRSTVWXYZ0' = ULID_ENCODE(ULID_DECODE('00123456789JKMNPQRSTVWXYZ0'));
select '000000000000JKMNPQRSTVWXYZ' = ULID_ENCODE(ULID_DECODE('000000000000JKMNPQRSTVWXYZ'));
select '00JKMNPQRSTVWXYZ0000000000' = ULID_ENCODE(ULID_DECODE('00JKMNPQRSTVWXYZ0000000000'));
select '01JKMNPQRSTVWXYZ0000000000' = ULID_ENCODE(ULID_DECODE('01JKMNPQRSTVWXYZ0000000000'));
select '00000JKMNPQRSTVWXYZ0000000' = ULID_ENCODE(ULID_DECODE('00000JKMNPQRSTVWXYZ0000000'));
select '000000000JKMNPQRSTVWXYZ000' = ULID_ENCODE(ULID_DECODE('000000000JKMNPQRSTVWXYZ000'));
select '01234567890123456789012345' = ULID_ENCODE(ULID_DECODE('01234567890123456789012345'));
select '0JKMNPQRSTVWXYZJKMNPQRSTVW' = ULID_ENCODE(ULID_DECODE('0JKMNPQRSTVWXYZJKMNPQRSTVW'));
select '0MNPQRSTVWXYZMNPQRSTVWXYZ0' = ULID_ENCODE(ULID_DECODE('0MNPQRSTVWXYZMNPQRSTVWXYZ0'));
select '0ZYXWVTSRQPNMZYXWVTSRQPNM0' = ULID_ENCODE(ULID_DECODE('0ZYXWVTSRQPNMZYXWVTSRQPNM0'));
select '0WVTSRQPNMKJZYXWVTSRQPNMKJ' = ULID_ENCODE(ULID_DECODE('0WVTSRQPNMKJZYXWVTSRQPNMKJ'));
select '0000004JFGTYNCK4CFM2C8EXXS' = ULID_ENCODE(ULID_DECODE('0000004JFGTYNCK4CFM2C8EXXS'));
select '0000004JFGT6RQ0EYX5PEWWJHD' = ULID_ENCODE(ULID_DECODE('0000004JFGT6RQ0EYX5PEWWJHD'));
select '0000004JFGGMVJGZD53ZCSEV76' = ULID_ENCODE(ULID_DECODE('0000004JFGGMVJGZD53ZCSEV76'));
select '0000004JFGGMVJGZD53ZCSEV7B' = ULID_ENCODE(ULID_DECODE('0000004JFGGMVJGZD53ZCSEV7B'));
select '000XA16S41ACTAV9WEVGEMMVR8' = ULID_ENCODE(ULID_DECODE('000XA16S41ACTAV9WEVGEMMVR8'));
                                                              
select '2019-01-01 00:00:00' = ULID_TO_DATETIME(ULID_FROM_DATETIME('2019-01-01 00:00:00'));
select '2020-02-02 02:02:02' = ULID_TO_DATETIME(ULID_FROM_DATETIME('2020-02-02 02:02:02'));