International postcodes: requirements and status

The postal_code type, alongside the UK postcode type, in the postcode extension (2.0.0). Branch dev/postal_code-international.

Status as of 2026-10-03. All six requirements below are implemented, plus the extras listed after them. The README documents how each is used; sql/postal_code.sql / expected/postal_code.out are the regression tests.

Decisions that shape everything

  • The type is postal_code (it was drafted as intl_postcode; that name is dead).
  • The text form is the UPU form CC-postcode, CC being the ISO 3166 two-letter code (US-90210, GB-SW1A 1AA, FR-75008). The earlier CC: form is gone entirely. The country is always exactly two characters, so the first hyphen is the delimiter even where the national code has hyphens of its own (US-90210-1234, BR-01310-100).
  • A value is 64 bits: country (two 5-bit letters, so it sorts as ISO text) | format (6 bits) | national payload (48 bits). Values from different countries compare by ISO order; within a country, text order. A coarser value sorts immediately before the finer ones that extend it.
  • Which format a country uses is a live SQL table, not compiled in.

The requirements

  1. Separator CC- instead of CC: – done (91d8a63).

  2. Constructor taking (postcode, cc); cc may be NULL when the postcode carries a CC- prefix; an error if they disagree – done (bb04ec2). postal_code(postcode, cc); the same rules apply to to_postal_code(postcode, cc) and is_valid_postal_code(postcode, cc), which give NULL / false where the strict form raises.

  3. Where there is a distinct outcode and incode (UK, Ireland, Canada, USA), the outcode alone is a valid postcode; where the leading digits are only implicitly an outcode (France), the full code is required – done (91d8a63). GB-SW1A, IE-D02, CA-K1A, US-90210 are values. FR, CZ and LU require the whole code. GB (also GG, IM, JE) and IE were added to make this meaningful.

  4. An outcode() function, with district() as an alias – done (088350c). Returns the area part as a complete postal_code (GB-SW1A 1AA -> GB-SW1A; a ZIP+4 -> its ZIP5; Eircode -> routing key). NULL for formats with no distinct outcode and for the end-of-country bound. IMMUTABLE, so CREATE INDEX ON t (outcode(pc)) works.

  5. upper_bound() returns a valid postcode where possible; otherwise explore the repercussions – done (b90f9b7). upper_bound('GB-LS24') is the smallest valid value past everything starting with it. At the top of a country there is none, and a range’s own “no upper end” would run on into the next country, so the bound there is the end-of-country bound CC-~: a value that sorts after every real postcode of its country and before the next, accepted and shown as text, but not a postcode (is_valid_postal_code rejects it). It is a bound only. Also delivered: lower_bound(), the native range type postal_code_range, and postal_prefix().

  6. Per-country character and numeric restrictions detected at ingest and exposed as is_valid_postal_code() – done (e361848). Each format enforces its own rules at input (Canada’s excluded letters, the UK area table, Eircode’s alphabet, US ZIP+4 0000, …). is_valid_postal_code(postcode, cc) (the country is optional) is exactly to_postal_code(...) IS NOT NULL.

Added beyond the requirements

  • to_postal_code() (bf34cee) – NULL-returning counterparts of the strict constructors, for loading dirty feeds (the topostcode() analogue). France accepts and normalises CEDEX.
  • Partial match and ranges (b90f9b7) – every prefix of a code is one contiguous range, so pc <@ postal_prefix('GB-LS24') is a btree range scan. A planner support function folds constant fragments, and a trigger invalidates cached plans if a country’s format is reassigned.
  • The % / !% operator – the UK type’s partial-match operator, ported; lenient for bad fragments, rewritten to a range for index use.
  • Locking a column to a country – pc postal_code('US'), as PostGIS locks a geometry column to an SRID. Enforced on INSERT/UPDATE/COPY (text and binary), ::postal_code('US') and ALTER COLUMN ... TYPE. COPY may use bare national codes; INSERT literals need the prefix (a PostgreSQL limit). The postal_code_columns view lists the locks.
  • Ranked patterns – add_country_template('PL', 'NN-NNN') or add_country_template('NL', '/[1-9]\d{3}( [A-Z]{2})?/') onboards a country with no C and no rebuild. A pattern (a template, or a bounded regular expression: classes, ?, {n,m}, groups, alternation, no * + .) defines the finite set of the country’s codes, and a code is stored as its rank in text order: order, validity and prefix ranges are exact for any pattern, with real codes as range bounds and no wasted bits (at most 248 codes, 40 characters). A pattern is a permanent language of its country (postal_code_languages, 51 versions per country; a stored value carries the version as its format). Engine: postal_code_pattern.c, Postgres-free, tested against a brute-force oracle (28 million checks, test_postal_code_pattern.c).
  • Every country in the world: 194 of 250 ISO entries have a built-in format (the UAE’s holds Abu Dhabi’s 5-digit codes and Dubai’s Makani location numbers, labelled as such), the other 56 are recorded as having no postal codes (postal_code_world). Formats come from the GeoNames data (120 countries, 1.65 million codes, all load but 21 French non-codes, one American Samoa ZIP and the UK’s W1M) and Wikipedia’s list of postal codes; tools/world_formats.py generates the table. Countries that write their ISO letters into the code (VG1110, AD500) accept them in front on input (checked, not stored). Country rules narrower than the code’s shape (Turkey’s provinces, 980NN for Monaco, single-code territories, the Dutch letter sets, Taiwan’s three lengths) are in the patterns, each adopted only after it rejected nothing in GeoNames (NARROWING.md).
  • Countries and formats are data: the postal_code_country_formats view, add_country_format(), remove_country_format(). Shipped assignments and user assignments are kept apart, so a dump carries everything a user added (templates and country assignments). One caveat: restoring needs those tables loaded before any postal_code data (a reordered restore list, documented in the README), because the text input depends on them.
  • Formats implemented in C: US, CA, FR, BR, CZ, LU, GB (also GG/IM/JE), IE. Anything else is a pattern.
  • Validated on real data – 117 million OpenStreetMap postcodes, 1.8 million GeoNames codes, 24,000 Companies House addresses and an upgrade of 829,000 real UK postcodes; see VALIDATION.md for the method, results, the bugs it found (pg_dump failed on any database with the extension) and the gaps that remain. tools/osm_validate.sql re-runs it.
  • Test suite as part of the package: the pg_regress test postal_code, plus standalone harnesses (test_postal_code.c, test_postal_code_range.c, test_postal_code_pattern.c) that need no Postgres.

Planned or open

  • Restore ordering. pg_restore loads table data in name order, so user data can be read before the assignments it needs. A reordered restore list works (README); making it automatic would need a dependency pg_dump understands, which the extension mechanism doesn’t offer.
  • Reviewing the Wikipedia-only formats. About 70 countries have no GeoNames rows, so their format is Wikipedia’s alone (basis Wikipedia in postal_code_world); a few (Egypt, Myanmar, Vietnam, Israel) accept two lengths because the sources disagree. They need checking against real data when it is available.
  • Validating cc against the ISO 3166 list when a country is assigned, to catch typos and retired codes.
  • Sharjah’s PCS, and the UAE’s emirates generally. The UAE has several schemes and AE holds one format (NNNNN[ NNNNN]: Abu Dhabi’s 5-digit codes and Dubai’s Makani numbers), so Sharjah’s PCS is not modelled. Adding it needs its layout; the type is keyed by country, so per-emirate formats would be a larger change.
  • Pattern limits: at most 248 codes and 40 characters, no unbounded repetition, and no lists of valid codes (the UK’s outcodes, the US’s valid ZIP prefixes). Separators dropped from outcode or sector text are ambiguous (SW11 is read as the outcode SW11, not SW1 1); a full code is not.
  • Ranges over historical formats: range bounds cover a country’s current format only, since the format bits sit between country and payload.
  • A GiST subtype_diff for postal_code_range, for GiST-indexed range queries.
  • Releasing: merge dev/postal_code-international to main (or open a PR). The 1.3.5 -> 2.0.0 upgrade is tested on 829,000 real UK postcodes and pg_dump/pg_restore on that database (VALIDATION.md).