Generates ordered, base36 alpha-numeric ids similar to Slack's ID scheme. Suitable for use as primary key.

hid_generate.sql

-- This function generates ordered, base36 alpha-numeric ids similar to Slack's ID scheme.
--
-- I wanted a primary key scheme that had the following features:
--   1) Lexical order, so that `ORDER BY` works as expected.
--   2) Prevents sampling an auto-incrementing primary key to determine growth over time.
--   3) Shorter and more human-friendly than BIGINT and UUID keys.
--   4) Has a prefix such that table can be inferred from any record's primary or foreign key.
--
-- It is suitable for use as primary key, provided a few assumptions are true:
--   1) You do not attempt to genereate more than 10M hids per second (system-time).
--   2) If system clock is reset backwards, do not violate assumption #1.
--   3) System clock is never set to a time prior to Jan 1, 1970.
--
-- To use, define your table like:
-- ```
-- CREATE SEQUENCE examples_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
-- CREATE TABLE examples (
--  id character varying DEFAULT hid_generate('E', 'examples') NOT NULL
-- )
-- ```

CREATE FUNCTION hid_generate(prefix character varying, tbl_name character varying) RETURNS character varying LANGUAGE plpgsql AS $$
DECLARE
  alphabet constant text[] := string_to_array('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'::character varying, null);
  hid character varying := '';
  seq_id bigint;
  unix_ms bigint;
  remainder bigint;
BEGIN
  -- low characters are sequence based so we can generate up to 10,000 unique hids per millisecond 
  SELECT nextval(tbl_name || '_id_seq') % 10000 INTO seq_id; 
  LOOP
    remainder := seq_id % 36::bigint;
    seq_id := seq_id / 36::bigint;
    hid := '' || alphabet[(remainder + 1)] || hid;
  EXIT WHEN seq_id <= 0;
  END LOOP;

  -- high characters are time based such that hids are in order of creation
  SELECT ABS(FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000)) INTO unix_ms;
  LOOP
    remainder := unix_ms % 36::bigint;
    unix_ms := unix_ms / 36::bigint;
    hid := '' || alphabet[(remainder + 1)] || hid;
  EXIT WHEN unix_ms <= 0;
  END LOOP;

  RETURN UPPER(prefix) || hid;
END;
$$;
COMMENT ON FUNCTION hid_generate IS 'Generates ordered hash ids: alpha-numeric, base36 strings.';