Contents

• codesystem
  • Vocabulary
  • Decisions so far (John, 2026-10-10)
  • Reusing from postal_code
  • Status and layout
  • Next
  • Status: steps 2 and 3 (0.1.0, not yet released)
  • Pattern systems (0.2.0)

codesystem

Working draft. A PostgreSQL extension for code systems: SIC, SOC, ISIC, NACE, ISCO, NUTS, GSS, ICD, HS … – sets of codes with a hierarchy and, often, a lineage. Exact validation, compact storage, ordering and prefix ranges that follow the hierarchy, and first-class support for derived systems (a national variant of an international standard).

It is separate from postal_code (which is for postal codes) and shares none of its vocabulary.

Vocabulary

Term	Meaning
code system	a named, versioned set of codes: isic_rev4, nace_rev2, uk_sic_2007, isco_08, uk_soc_2020
code	one member of a code system, a string whose width belongs to its level
level	a rank in the hierarchy, with a name (section, division, group, class, subclass)
derived from	a system built on another: refines (its codes are the parent’s plus more characters) or corresponds (a mapping)
crosswalk	the table of correspondences between two code systems, with its cardinality

Decisions so far (John, 2026-10-10)

• Its own extension and functions; postal_code terminology is not used for anything else. Name: codesystem.
• One generic type, code('uk_sic_2007'), not one type per system: a roll-up or translation must return a typed value, and that is not possible when every system is its own type. A column locked with code('uk_sic_2007') refuses any other system.
• Derived standards are modelled as refines (validated at definition: every derived code’s prefix must be a parent code; fail loud) or corresponds (a crosswalk). Do not assume refinement: ISIC Rev.4 to NACE Rev.2 is probably not a strict prefix refinement (ISIC 4510 vs NACE 4511 / 4519); let the data decide.
• A code is a string with a fixed width per level. Real data loses leading zeros (see data/SOURCES.md); the loader must say so.

Reusing from postal_code

The ranked-pattern engine (postal_code_pattern.c/h in https://github.com/John-Cameron-Burns/pg-postcode): rank = position among the codes in byte order, so order, validity and prefix ranges are exact; the sorted-list builder (the smallest automaton for a list, pc_pattern_from_list, branch feat/list-sql there); named parts. This project vendors a renamed copy; the symbols are hidden, so both extensions can be loaded together.

Status and layout

Step 1 of the build is done: the engine is vendored and the first two source files are loaded.

engine/cs_pattern.c, .h     the ranked-pattern engine (vendored, renamed cs_*; Postgres-free)
test/test_cs_*.c            its tests (28 million checks), copied with it and renamed
tools/csmodel.py            what a prepared code system is, and the checks every loader must pass
tools/prepare_isic.py       ISIC Rev.4  (UN)  -> data/prepared/isic_rev4.csv
tools/prepare_isco.py       ISCO-08     (ILO) -> data/prepared/isco_08.csv
test/test_prepare.py        the loaders: synthetic defects on purpose, and the real files if fetched
data/SOURCES.md             where the source files come from, and what was found in them

make test        engine tests + loader tests          make prepare    build data/prepared/ from data/raw/
make sanitize    engine under ASan/UBSan (on Linux)

The prepared form is code, level, parent, label, validated against the publisher’s own counts (ISIC Rev.4: 21 / 88 / 238 / 419; ISCO-08: 10 / 43 / 130 / 436). Nothing is repaired silently: the one repair so far (ISCO’s lost leading zeros in the Armed Forces group) is reported on every run.

Not yet verified: the vendored engine under the sanitizers (the code is unchanged from postal_code, where it is clean, but make sanitize has not been run here: AddressSanitizer hangs at start-up on the development Mac). Set up CI before relying on it.

Next

1. The extension: a registry of code systems, the generic code('system') type (system id + rank in the value), levels, loading from the prepared form, part() / rollup(); ISIC and ISCO-08 as the first systems.
2. Derivation: refines (checked at definition), rollup(), and the corresponds crosswalk (real data: SIC 2003 to 2007).

Status: steps 2 and 3 (0.1.0, not yet released)

Working and tested on PostgreSQL 17 (regression tests define types hierarchy revisions; engine suites also clean under AddressSanitizer/UBSan on Linux): the registry, define_code_system(), the code type with its column lock, ordering and indexes, binary I/O, code_level/parent/ancestor/children/label, revisions, and ISIC Rev.4 and ISCO-08 loaded from tools/load_systems.sql with the publishers' counts checked.

Things to know:

• Write the system in SQL text. PostgreSQL gives a literal’s input function no type modifier, so INSERT ... VALUES ('isic_rev4:4711') is needed; a bare '4711' is read from text (COPY, a text column, '4711'::text, code_from('4711','isic_rev4')) into a column declared code('isic_rev4').
• Restoring a dump needs the codesystem_* tables first: use tools/restore.sh dump newdb (custom-format dumps). Plain pg_restore fails loudly if the order is wrong; it never loads wrong data.
• Values of different revisions are different values, even for the same code text (the rank can differ). A code_current() normaliser is the next thing to add.
• Derived standards (step 3). define_code_system(..., refines => 'nace_rev2') makes a national variant: its top levels must be exactly the base’s (same levels, same codes, same parents), checked at definition with every difference reported; it may add deeper levels. code_rollup(code, 'nace_rev2') follows the chain upwards.
• Corresponding standards. define_crosswalk('sic2003','sic2007', pairs_query) ties systems that are not derived from one another; every code must exist and every finest-level code should have a counterpart (or allow_unmapped). code_map(code, target), code_map_unique() and the view code_crosswalks (one-to-one / many-to-one / …).
• Revisions. code_current(c) and code_is_current(c) bring values written under different revisions together.

Pattern systems (0.2.0)

An identifier with a syntax and no list – the Companies House company number – is a pattern system:

SELECT define_code_system_pattern('uk_crn', '/\d{8}|(?<prefix>SC|NI)(?<number>\d{6})/', 'number');
SELECT code_part('uk_crn:SC123456', 'prefix');                       -- SC
SELECT code_from_padded('1234567', 'uk_crn');                        -- uk_crn:01234567
WHERE crn BETWEEN code_first_with_prefix('SC','uk_crn') AND code_last_with_prefix('SC','uk_crn')   -- every SC number

A code is its rank among everything the pattern allows, so numbers issued later change nothing and no revisions are needed. A pattern says what can be a number, not which have been issued. tools/load_crn.sql is the real definition (see docs/CRN.md). Upgrade from 0.1.0 with ALTER EXTENSION codesystem UPDATE.