Creating pretty, concise and unique base 62 ids

gistfile1.txt

// Not sure where I found this approach. Probably a combination of multiple kind people's advices.
// Basically we make a random number or something and encode it as 62 bit, for concise URL ids.
// I don't recall how uniqueness is guaranteed but I think its because we are basing the ID off the timestamp. You could also add a unique constraint on your DB column of course.

CREATE sequence global_id_sequence MINVALUE 0 MAXVALUE 1023 START 0 CYCLE;

CREATE OR REPLACE FUNCTION generate_number(OUT number_id bigint) AS $$
    DECLARE
        shard_id int := 1;
        start_epoch bigint := 1498490095287;
        calculated_time bigint;
        sequence_id bigint;
    BEGIN
        SELECT nextval('global_id_sequence') INTO sequence_id;
        SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO calculated_time;
        number_id := ((calculated_time - start_epoch) << 23)  | (shard_id <<10) | (sequence_id);
    END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION encode_base62(IN base_10 bigint, OUT base_62 text) AS $$
    DECLARE
        base int := 62;
        characters char[] := ARRAY['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z', 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9'];
        quotient bigint;
        digits char[];
    BEGIN
        quotient := base_10;

        WHILE quotient/base >= 1 LOOP
            digits := characters[(quotient % base) + 1] || digits;
            quotient := floor(quotient/base);
        END LOOP;

        digits := characters[quotient] || digits;
        base_62 := array_to_string(digits, '');
    END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION make_id() RETURNS TRIGGER AS $$
BEGIN
    NEW.id := encode_base62(NEW."numericId");
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

// Then use it. In my DB I give entries both a serial ID I belive, AND this concise 62 bit ID. Not sure why I kept both.
ALTER TABLE "Profiles" ADD COLUMN "numericId" bigint DEFAULT generate_number() NOT NULL;
CREATE TRIGGER profile_id_transform BEFORE INSERT ON "Profiles" FOR EACH ROW EXECUTE PROCEDURE make_id();