pgcrockford - Crockford Base32 encoding for PostgreSQL unsigned integers
The pgcrockford extension provides Base 32 encoded integers as a PostgreSQL base type. From Douglas Crockford's requirements for the specification, the encoding should
- Be human readable and machine readable.
- Be compact. Humans have difficulty in manipulating long strings of arbitrary symbols.
- Be error resistant. Entering the symbols must not require keyboarding gymnastics.
- Be pronounceable. Humans should be able to accurately transmit the symbols to other humans using a telephone.
The motivation for
pgcrockford is to provide a more humane display of
values used for surrogate keys while preserving the efficiency of an
crockford values are represented as
alpha-numeric strings while using unsigned integers for their
Releases and Dependencies
Latest release: 0.8.34
Tested with PostgreSQL 11.
make make install make installcheck
CREATE SCHEMA crockford; CREATE EXTENSION crockford WITH SCHEMA crockford;
Note: The extension doesn't require a dedicated schema, but it's good practice to utilize schemas to namespace modules.
pgcrockford extension provides 3 base types: 2-byte, 4-byte, and
-- interpret '10' as a crockford literal SELECT '10'::crockford.crockford4; -- 10 -- cast integer 10 to crockford SELECT 10::crockford.crockford4; -- A
CREATE TABLE store.widgets ( widget_id crockford.crockford4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, widget_name text UNIQUE NOT NULL ); INSERT INTO store.widgets (widget_name) SELECT 'widget' || n FROM generate_series(1,64) AS _ (n); SELECT * FROM store.widgets ORDER BY widget_id DESC LIMIT 10; /* widget_id | widget_name -----------+------------- 20 | widget64 1Z | widget63 1Y | widget62 1X | widget61 1W | widget60 1V | widget59 1T | widget58 1S | widget57 1R | widget56 1Q | widget55 (10 rows) */
Getting funky with representation
One of the drawbacks of using sequences as surrogate keys is that
there's nothing to distinguish an id value of one table from an id
value of another. If both
id surrogate key columns, is
widget_id or a
location_id? Setting aside debates of whether one should use
surrogate keys or column naming conventions, you can leverage the
alpha-numeric representation of the
crockford types to encode the
type in the id value.
CREATE FUNCTION store.next_widget_id() RETURNS crockford.crockford4 LANGUAGE sql AS $body$ SELECT 'W00000' + nextval('store.widgets_widget_id_seq'); $body$; CREATE FUNCTION store.next_location_id() RETURNS crockford.crockford4 LANGUAGE sql AS $body$ SELECT 'L00000' + nextval('store.locations_location_id_seq'); $body$;
These functions will provide 33,554,431 unique values each
LZZZZZ respectively) before
encroaching on the encoded "type".
Operators and search_path
If you load crockford into a schema that's not on the
the crockford-specific operators won't be available without specifying
the schema explicitly. This is normal behavior for
PostgreSQL, but may be suprising if you haven't seen
SELECT 'A'::crockford.crockford4 + 1; -- 11 -- specify the schema explicitly using OPERATOR SELECT 'A'::crockford.crockford4 OPERATOR(crockford.+) 1; -- B SET search_path TO crockford; SELECT 'A'::crockford4 + 1; -- B
If you're using crockford a lot, you'll likely want to either
explicitly set the
search_path to include the crockford extension
schema or set the default
search_path for your
database. For example,
ALTER ROLE grzm SET search_path TO 'crockford';
You can also set
search_path per role:
ALTER DATABASE crockford_test SET search_path to 'crockford';
The fine PostgreSQL manual includes more discussion of schema usage.
pgcrockford extension takes inspiration from Peter Eisentraut's
pguint library, both for using unisigned integers as the
underlying implementation and also for generating some of the code
that implements it (see generate.py).
© 2019 Michael Glaesemann
Released under the PostgreSQL License. See LICENSE file for details.