Postcode 1.3
============
UK postcode encoded in 32 bits and optimised for indexing and partial matches


Coverage
--------
Supports all 127 postcode areas. The crown dependencies GY, JE and IM and
Gibraltar's GX area are
included plus two non-geographic areas BX and BF.

The type should support all current and future codes with the sole exception
of the atypical code GIR 0AA, which Royal Mail nolonger includes within the
postcode address file. In the unlikely event further postcode areas are added
the type is extensible to a maximum of 255 areas (see areas.h)


Parsing
-------
Text input must match one of the above areas followed by a correctly specified
district, sector and walk. Although the type enforces entry of a code with the
correct components, confirming a code is allocated for use requires an external
data source such as [Code-Point Open](http://www.ordnancesurvey.co.uk)

The text parser is relatively tolerant of varied formatting and will correct
for capitalisation and a variable number (or lack of) spaces between the
outward and inward codes.


Rendering
---------
When rendering postcodes to text the default output is upper case with a
single space between the outcode and incode. If an alternative format is
required a to_char() function is provided. The default output is equivalent
to calling to_char(postcode, 'AD SW');

Rendering never fails as of 1.3.3. A field that isn't a valid, complete
value -- which in practice means a range_lower()/range_upper() boundary
from a fragment short enough to leave something genuinely unfillable, or
truly corrupted data -- renders as `?` for whichever specific character
isn't available, rather than raising. A normal, complete postcode is
entirely unaffected (including the common case of a one-digit district,
e.g. `'SW1 1AA'`, which was always valid and still renders exactly as
entered).


Partial matching
----------------
For partial match queries using the % operator any potential ambiguity is
resolved by allocating the maximum number of digits to the district unless an
explicit space is placed between the district and sector.

For example % 'LS24' returns all postcodes in district LS24. To return all
codes in sector 4 of LS2 use % 'LS2 4'.

Neither or both characters of the walk must be entered. A partial search
including them is functionally equivalent to the equality = operator.

1.0 through 1.2, % was a plain function call, index-accelerated for
neither a constant nor a variable fragment. 1.3.0 registered it under
btree strategy 3 (the "equality" slot) to try to change that, but this
produced wrong results whenever an index existed on the column (upstream
issue #3): btree relies on strategy-3 matches being reflexive/
interchangeable, which a partial match isn't -- two different postcodes
can both match the same fragment without being equal to each other. 1.3.1
removed that registration, restoring correctness but leaving % unindexed
again either way, same as 1.0-1.2. Since 1.3.3, % gets an index-assisted
plan for a *constant* fragment (the common case: `code % 'LS24'`, not
`code % some_column`) via a planner support function that rewrites the
call into the range_lower()/range_upper() form below at plan time --
`EXPLAIN` will show a normal index scan, `code`'s own value is what's
compared, no opfamily trickery, none of 1.3.0's soundness bug. A
non-constant fragment still runs the plain, always-correct, unindexed
function, exactly as it always has. See Indexing below for the range
form itself, which is still the thing to reach for directly if you want
that plan guaranteed rather than inferred.


Indexing
--------
Standard B-tree operators are supported. The sort order is consistent with
that of the type rendered to text format using the C locale.

For an indexed partial match, use range_lower()/range_upper() instead of
%: they express a fragment as a genuine half-open range, using the
ordinary (and ordinarily correct) </>= operators for full index support:

    SELECT * FROM addresses
    WHERE postcode >= range_lower('LS24') AND postcode < range_upper('LS24');

Unlike %/!%, range_lower()/range_upper() raise an error on an invalid
fragment rather than silently returning a value -- they're meant to be
called with a literal, known-good fragment when constructing a query, not
with arbitrary/untrusted input.

Since 1.3.3, both bounds are real, valid, directly-renderable postcodes,
not raw internal values -- `range_lower('LS1')` is `'LS1 0AA'`,
`range_upper('LS1')` is `'LS10 0AA'` (the lowest real postcode of
whichever fragment immediately follows LS1 -- not, perhaps
counter-intuitively, `'LS2 ...'`: LS1 and LS10 share the same first
district digit and sort adjacently, LS2 doesn't come until every LS1x
district is exhausted -- the type's sort order matches the text form's
own left-to-right character order, so this is the same ordering
`ORDER BY postcode` or a plain text comparison would already give you).
This is why `EXPLAIN`'s output for the % rewrite above reads as real
postcodes too. The interval is always half-open and exclusive at the top
-- `range_upper(X)` is never itself included by a `< range_upper(X)`
comparison -- so it being a real postcode (specifically, the real lower
bound of whatever comes next) is exactly the tiling property that makes
adjacent fragments' ranges meet with no gap and no overlap, not a loose
end.

A B-tree index for the encoded type will be approximately 25% smaller than
an equivalent index on a column of type text. This may give a performance
advantage where the index can therefore be held entirely within memory.

Since 1.3.4, `<`/`<=`/`>`/`>=` (both `postcode` and `dps`) carry real
selectivity estimators (PostgreSQL's own standard `scalarltsel`/
`scalarlesel`/`scalargtsel`/`scalargesel` and join counterparts, the
same ones `int4`/`text`/`date` use for these operators) -- previously
unset entirely, which meant the planner had no way to use `ANALYZE`'s
own column statistics for a range predicate at all, regardless of how
accurate or fresh those statistics were, and would fall back to a fixed
default guess. Confirmed live against a real 34.8M-row table with an
existing plain `btree(postcode)` index and fresh statistics: the same
`BETWEEN range_lower/range_upper` query went from a ~39s sequential scan
to a 270ms index scan, no new index, no query change -- purely from the
planner now being able to see the real distribution.


Casting to/from text
---------------------
postcode::text and 'SW1A 1AA'::postcode already work without any of
this -- PostgreSQL falls back to any type's own input/output functions
for the `::` syntax even with no cast registered at all. What that
implicit fallback *doesn't* do is participate in ordinary function-
argument resolution or PostgreSQL's own internal dependent-object
rewriting (notably `ALTER COLUMN ... TYPE`'s automatic index rebuild) --
both need a real `pg_cast` entry to work, which is what this adds.

    postcode AS text   -- IMPLICIT
    text AS postcode   -- ASSIGNMENT
    dps AS text        -- IMPLICIT
    text AS dps        -- ASSIGNMENT

Deliberately asymmetric. `<type> AS text` is IMPLICIT because that's the
direction real call sites actually need auto-coerced -- a functional
index like `split_part(postcode_col::text, ' ', 1)` needs `postcode_col`
to coerce into `split_part`'s text parameter automatically for
PostgreSQL to be able to re-resolve it during `ALTER COLUMN TYPE`'s
index rewrite, and PostgreSQL only ever auto-applies an IMPLICIT cast
for that kind of resolution -- an ASSIGNMENT cast (tried first) is only
auto-applied for INSERT/UPDATE target-column coercion, not general
argument matching, and doesn't actually fix this. `text AS <type>` stays
ASSIGNMENT: nothing in practice needs an arbitrary text value silently
coercing *into* postcode/dps in general expression contexts, and that
direction can raise on invalid input, which is a worse thing to have
fire implicitly than a render that always succeeds for a valid value.

Behaviour is otherwise unchanged either direction -- postcode_to_text()/
dps_to_text() are exactly what ::text already did; text_to_postcode()/
text_to_dps() are exactly what ::postcode/::dps already did, same strict
raise-on-invalid-input as always. topostcode() remains the NULL-safe
alternative for messy/untrusted input, unaffected by any of this.


Delivery point suffixes
-----------------------
For any postcode there is a maximum of 175 delivery points, each of which
is allocated a suffix of the form [1-9][A-Z] with the characters CIKMOV not
used. Suffixes follow the sequence 1A, 1B, 1C through to 9T. Codes 9U-9Z
are for use by applications as defaults where the correct suffix is unknown.

A suitable type (dps) is provided which encodes into one byte all possible
values, including codes 9U-9Z. Parsing is case insensitive but output is
always in upper case.

The type aims to provide strict validation rather than space efficiency,
although some small storage savings can be made compared to char(2) if
careful ordering of columns is made with respect to alignment.


Binary format
-------------
For client applications exchanging results in binary format the functions
declared in binfmt.h can be used for parsing from or rendering to text format


postal_code: any country, not just the UK
------------------------------------------
Since 2.0.0, this same extension also provides `postal_code`, a 64-bit type
covering any country's postal code -- additive alongside `postcode`, not a
replacement for it. Existing `postcode`/`dps` columns, indexes and binary
data are entirely unaffected by installing or upgrading to 2.0.0.

    SELECT 'US-90210-1234'::postal_code;   -- ZIP5 + optional ZIP+4
    SELECT 'CA-K1A 0B1'::postal_code;      -- full FSA+LDU
    SELECT 'CA-T0A'::postal_code;          -- the outcode alone is a complete value
    SELECT 'GB-SW1A'::postal_code;         -- ... as it is for GB and IE
    SELECT postal_code('90210', 'US');     -- two-argument constructor for
                                            -- separate code/country columns,
                                            -- analogous to PostGIS's
                                            -- ST_GeomFromText(wkt, srid)
    SELECT country('US-90210'::postal_code);  -- 'US'

The text form follows the UPU recommendation: the ISO 3166-1 alpha-2 country,
a hyphen, then the national code (`CC-code`). A country code is always
required -- `'90210'::postal_code` raises, there is no implicit default
country. Because the country is always exactly two characters, the *first*
hyphen is the delimiter even when the national code has hyphens of its own
(`US-90210-1234`, `BR-01310-100`). The colon form (`US:90210`) is not accepted.

**Outcode-only is a valid postcode** wherever a country has a distinct
outcode/incode structure -- GB, IE, CA and US (`GB-SW1A`, `IE-A65`,
`CA-K1A`, `US-90210`). Real data backs this: every one of GeoNames' 27,450 GB
rows and all 139 IE rows is outcode-only. Where the leading digits are only
implicitly an outcode (FR, CZ, LU) the full code is required. What is *not*
valid is the in-between: `GB-SW1A 1` (a sector with no unit) is a fragment.

`postal_code(postcode, cc)` takes the country from `cc`, from the postcode's own
`CC-` prefix, or from both -- in which case they must agree:

    SELECT postal_code('90210-1234', 'US');      -- country from cc
    SELECT postal_code('US-90210-1234', NULL);   -- ... or from the prefix; cc may be NULL
    SELECT postal_code('US-90210-1234', 'US');   -- ... or both, if they agree
    SELECT postal_code('US-90210', 'CA');        -- ERROR: cc does not match the prefix
    SELECT postal_code('90210', NULL);           -- ERROR: no country anywhere

A prefix is exactly two letters then a hyphen, which no national format starts
with (Luxembourg's `L-1311` has one letter), so there is no ambiguity. A NULL
postcode gives NULL. `to_postal_code(postcode, cc)` and `is_valid_postal_code(postcode, cc)` follow
the same rules, giving NULL / false where the strict form would raise.

Countries sort in ISO 3166-1 alpha-2 **text** order unconditionally (`'CA-...'`
always sorts before `'US-...'`), regardless of how any given country's own
national code happens to be packed internally. Within one country, a more
precise variant of the same underlying code -- a ZIP5 vs. that same ZIP5 with
a +4, an outcode vs. a full postcode in it -- interleaves immediately next to
the value it refines, rather than being grouped apart from it by format.

One exception, in the UK format: its area list is append-only, so Gibraltar's `GX`,
added after `ZE`, sorts after `ZE` and not between `GR` and `HA` as the text would.
It is invisible within a country (Gibraltar is `GI`, whose only area is `GX`), but a
`GB-GX..` value would sort after every `GB-ZE..`. For the same reason a bare area
letter (`GB-A`) is not a fragment: the areas starting with it are not contiguous.

Formats implemented so far:

  * **US** -- 5-digit ZIP, with an optional `-NNNN` ZIP+4 add-on.
  * **CA** -- `ANA NAN` (e.g. `K1A 0B1`). The bare 3-character forward
    sortation area (e.g. `T0A`) is also a complete, valid value on its own,
    not a truncated fragment -- real-world data (GeoNames' worldwide postal
    code table) is overwhelmingly this shape for Canada, not the full 6
    -character form. D, F, I, O, Q and U never appear in any letter
    position; W and Z additionally never appear as the first letter.
  * **FR** -- 5 digits; a prefix is not a postcode. `CEDEX` is accepted and
    normalised away: `FR-75054 CEDEX 01` is stored and rendered as `FR-75054`.
    A CEDEX code is a real, distinct postcode (in GeoNames' FR rows, ~28% carry
    a CEDEX suffix, and nearly all of those 5-digit parts appear nowhere else
    as a bare code), and `CEDEX [n]` is address routing, not part of the code.
    The grammar is exactly `NNNNN`, `NNNNN CEDEX` or `NNNNN CEDEX n` -- not
    "ignore whatever follows the digits", so `75001 foo` is still an error. The
    few other oddities in that data (`SP 07`, `AIR`, `CITYSSIMO`: 21 rows of
    51,611) are rejected.
  * **BR** -- 5-digit base + optional 3-digit suffix (`NNNNN-NNN`, the CEP).
    Unlike US's `+4`, the suffix has no free value to use as an "absent"
    sentinel (`000` is itself a common real suffix), so presence is a
    dedicated bit rather than inferred from the value.
  * **CZ** -- 5 digits, conventionally rendered `NNN NN`.
  * **LU** -- `L-` + 4 digits; the `L-` is part of the canonical form here
    (unlike US/CA's own separators, which are input-only conveniences),
    since real Luxembourg data is essentially always written this way.
  * **GB** -- the existing `postcode` type's 32-bit value, carried unchanged in
    the payload and parsed by the same code (`postcode_parse`), so the two types
    cannot drift apart on layout. On top of that, `postal_code` enforces Royal Mail's
    letter rules, which `postcode` has never done: unit letters are never C I K M O V,
    the letter after the digit of an A9A outcode is one of A-H J K P S-U W, and of an
    AA9A outcode one of A B E H M N P R V-Y (so `NG12 4FO`, letter O for zero, is
    rejected). `'NG12 4FO'::postcode` is still accepted, as it always was. The outcode (`SW1A`, `LS24`) is a complete value. Also
    assigned to GG, IM and JE, whose areas (GY, IM, JE) are already in the UK
    layout.
  * **IE** -- Eircode: a routing key (`A65`, or the one exception `D6W`) that is
    a complete value on its own, optionally followed by the 4-character unique
    identifier (`A65 F4E2`). Every character comes from 25 symbols: the digits
    and the letters A C D E F H K N P R T V W X Y.

### Loading messy data

`::postal_code` and `postal_code(postcode, cc)` are strict: a bad value raises, which
aborts a whole `COPY`/`INSERT`. For feeds that contain rows which aren't valid
postcodes, `to_postal_code()` returns NULL instead (the role `topostcode()`
plays for the UK type), so the load can finish and the rejects can be found
afterwards. It comes in the same two forms as the strict constructors:

    SELECT to_postal_code('FR-75054 CEDEX 01');        -- like ::postal_code
    SELECT to_postal_code('75054 CEDEX 01', 'FR');     -- like postal_code(postcode, cc)

    INSERT INTO addresses (pc) SELECT to_postal_code(code, country) FROM staging;
    SELECT * FROM staging WHERE to_postal_code(code, country) IS NULL;

`is_valid_postal_code()` answers the same question as a boolean, in the same two forms (the country is
optional), for
`CHECK` constraints or for finding the rejects in a staging table (NULL in gives
NULL out, so a `CHECK` lets NULLs through):

    SELECT is_valid_postal_code('CA-D1A 0B1');        -- false: D is never used in a Canadian code
    ALTER TABLE staging ADD CHECK (is_valid_postal_code(code, country));

It is exactly `to_postal_code(...) IS NOT NULL`, so every per-country rule
(Canadian excluded letters, Eircode's alphabet, ZIP+4 `0000`, ...) is enforced
by the same parser at ingest and in `is_valid_postal_code()` -- they cannot disagree.

**What counts as the same code.** Real data spells one code several ways, so some
differences of spelling are not errors. Anything a format accepts as written is taken
as written. Only if that fails are these tried, in this order, and the first that
parses wins:

* the country's own letters dropped from the front -- `MH96960`, `AI 2640`,
  `US 90210`, `LU1471` (the country is already known). Jersey, Guernsey and Isle of
  Man codes start with their country's letters, but they are tried as written first,
  so `IM1 1AA` is unaffected and `IM1 SPT` is still rejected;
* spaces and hyphens swapped -- `1050 010` for `1050-010`, `L 1820` for `L-1820`;
* spaces dropped -- `06 830`, `19 801`, `K1A0B1` -- or dots, as in the Brazilian
  `06.026-170`.

Beyond that, a Brazilian CEP and a US ZIP+4 may be written without their hyphen
(`01139020`, `902101234`), which is how they usually are.

Before any of that the text is tidied: spacing at the ends, doubled spaces and
spaces next to a hyphen (`" us - 90210 "`), and, in a UTF-8 or SQL_ASCII database,
other scripts' spelling of the same characters: digits of other scripts become 0-9
(Persian `۱۱۴۱۶`, Arabic-Indic, Bengali `১২১৪`, Burmese `၀၇၀၉၁`, Devanagari `४००००१`,
Thai, full-width), the Unicode hyphens and minus signs become `-` (`050−0083`,
`064‐0915`), no-break and other Unicode spaces become a space, full-width letters
become ASCII, and zero-width characters and Japan's postal mark `〒` are dropped.
These only re-spell the same characters, so they can
recognise a code but never turn something that isn't one into one: a value that parsed
before parses to the same value now. Text *around* a code is a different job and is
not attempted -- `DE 19801`, `NSW 2000`, `ON L6M 0A8`, `1200-445 LISBON`, `CAP 00144`,
`PO BOX 3085` are all rejected. Pulling the postcode out of address text is data
cleaning, to be done before the value reaches this type.

### Locking a column to a country

Like PostGIS locking a geometry column to an SRID with `geometry(Point, 4326)`, a
column can be locked to a country with a type modifier:

    CREATE TABLE addresses (id serial, pc postal_code('US'));
    INSERT INTO addresses (pc) VALUES ('US-10001');     -- a prefix that agrees with the column
    COPY addresses (pc) FROM stdin;                     -- COPY may use bare national codes: 90210-1234
    INSERT INTO addresses (pc) VALUES ('CA-K1A 0B1');   -- ERROR: country "CA" does not match the column's country "US"
    SELECT * FROM postal_code_columns;                   -- which columns are locked to what

It is enforced wherever a value enters a column -- `INSERT`, `UPDATE`, `COPY` (text
and binary), `::postal_code('US')` and `ALTER COLUMN ... TYPE postal_code('US')`
(which fails if any existing value is from another country). An unlocked column
still requires the `CC-` prefix.

Bare national codes (no `CC-`) are accepted **only by `COPY`**: PostgreSQL passes a
string literal to the input function without the column's type modifier and applies
the modifier afterwards, so in `INSERT`/`UPDATE` the prefix is always needed. To load
bare codes with SQL use `postal_code('90210', 'US')`.

* It locks the **country**, not the format, so a country that moves to a new format
  keeps working in its column.
* As with any type modifier it is enforced on assignment and casts; a value merely
  returned from a function is not re-checked.
* The modifier's *shape* (two letters) is validated when the column is declared,
  but not whether the country is currently assigned a format. That is deliberate:
  the column definition has to survive a dump and restore before the data in
  `postal_code_country_formats` does. A lock to a country with nothing assigned is
  harmless -- every insert into it fails.
* There is no storage saving: the value is a fixed 8 bytes and the country stays in
  every row. The gain is integrity, and the bare-text convenience.

### Outcode

`outcode(pc)` (and `district(pc)`, the same function) is the area part of a
postcode **as a complete valid postcode of its own**:

    SELECT outcode('GB-SW1A 1AA');       -- GB-SW1A
    SELECT outcode('US-90210-1234');     -- US-90210
    SELECT outcode('CA-K1A 0B1');        -- CA-K1A
    SELECT outcode('IE-A65 F4E2');       -- IE-A65
    SELECT outcode('BR-01310-100');      -- BR-01310
    SELECT outcode(pc), count(*) FROM addresses GROUP BY 1;

It is idempotent (an outcode is its own outcode), and the outcode sorts before
every full code inside it. For FR, CZ and LU, where the leading digits are only
*implicitly* an outcode, there is nothing to extract and the result is NULL
rather than a whole code passed off as an area (the full code is required there,
as for validity). It is also NULL for the end-of-country bound, which is not a
postcode. `outcode()` is `IMMUTABLE` -- it reads only the value's own bits, never
the country table -- so it can be indexed: `CREATE INDEX ON t (outcode(pc))`.

### Partial match and ranges

A *fragment* is `CC-` plus a **prefix** of the national code -- `GB-LS24`, `FR-75`,
`CA-K1A 0` -- and matches every value that starts with it. Each format orders its
values exactly as its text sorts, so a prefix is one contiguous range, and
neighbouring prefixes tile with no gap and no overlap. A fragment is not a value:
`FR-75` is a fragment but not a postcode.

    SELECT * FROM addresses WHERE pc <@ postal_prefix('GB-LS24');   -- the range type
    SELECT lower_bound('CA-K1C'), upper_bound('CA-K1C');             -- CA-K1C, CA-K1E

* `postal_prefix(fragment)` returns a `postal_code_range`, a native PostgreSQL
  range type, so `<@`, `@>`, `&&`, `-|-` (adjacent) and multiranges all work.
* `lower_bound()` is the smallest *valid* value in the range (inclusive), e.g.
  `lower_bound('CA-K')` is the outcode `CA-K0A`. `upper_bound()` is the smallest
  valid value *past* it (exclusive), skipping symbols a format never uses
  (Canada's `D F I O Q U`, Eircode's missing letters) and carrying into the next
  sibling at the level above: a real postcode wherever a successor exists.
* **The top of a country has no successor value**, e.g. `US-99`, `FR-9`, the last UK
  area. There the upper bound is that country's **end-of-country bound**,
  written `US-~`: a value that sorts after every real value of the country and
  before the first of the next (`~` is the highest printable character, so it sorts
  last as text too). So `postal_prefix('US-99')` is `[US-99000,US-~)`, and
  `pc < upper_bound(...)` is right at the top of a country as well. A range is never
  left open-ended, because PostgreSQL's "no upper end" means the end of the *whole*
  value space and countries lie end to end in it: `[BR-99000,)` would run on through
  CA, CZ, ... US. (Found by comparing every prefix in 148k real rows against a plain
  text `GROUP BY`; a design that used an open end looked right only while the country
  in question was the last one assigned.)
* Bounds are bounds, not addresses. A bound that is the successor of a prefix need
  not be a code anyone has, and using one as a postcode is bad practice. `US-~` is
  accepted as input so a stored range survives a dump and restore, but nothing treats
  it as a postcode: `is_valid_postal_code('US-~')` is false and `to_postal_code('US-~')` is NULL.
  (`'US-'` with nothing after the hyphen is still an error, so an empty code in a
  concatenation cannot quietly become one.)
* The UK rule is kept: `GB-LS1` is district LS1 only, not LS1x (all available
  digits go to the district unless a space says otherwise). GB's area list is
  append-only, so "the next area" is the next in *encoding* order (`ZE` is followed
  by `GX`) -- the order the values themselves compare in, so tiling still holds.

**Index use.** A call with a constant fragment is folded into a constant range at plan time.
From PostgreSQL 17, PostgreSQL's own rewrite of `col <@ <constant range>` into plain btree
conditions (`pc >= lo AND pc < hi`) then applies, so `pc <@ postal_prefix('GB-LS24')` uses a
btree index on `pc`. **Before 17 (14, 15 and 16) that query is correct but is a filter.** On
every version, `pc % 'GB-LS24'` and `pc >= lower_bound('GB-LS24') AND pc < upper_bound('GB-LS24')`
use the index (the latter because a `STABLE` function of a constant is evaluated once), so
use one of those if you support servers older than 17.
`postal_prefix()` is `STABLE` rather than `IMMUTABLE` because the answer
depends on which format a country is assigned, so the folded plan is made to depend
on `postal_code_country_formats` and a trigger invalidates cached plans whenever
that table changes -- a reassigned country cannot leave a stale plan behind (this
is tested with a prepared statement).

### The `%` operator

`pc % 'GB-LS24'` is true when `pc` starts with the fragment, and `pc !% 'GB-LS24'`
when it doesn't -- the UK type's operator, ported. It means the same as
`pc <@ postal_prefix('GB-LS24')`, with the UK operator's leniency: a fragment that
isn't one (no `CC-`, an unassigned country, not a prefix of that format) matches
nothing, and `!%` matches everything, because `%` is meant for arbitrary input such
as a search box where an error would be the wrong answer. `postal_prefix()`,
`lower_bound()` and `upper_bound()` still raise on a bad fragment.

With a constant fragment the planner rewrites `pc % 'fragment'` into
`pc >= lo AND pc < hi`, so it uses a btree index through the ordinary sound
strategies. `%` is deliberately *not* registered in the btree operator family (it
is not an equivalence relation: two different codes can both match one fragment).
Like `postal_prefix()`, the rewritten plan depends on `postal_code_country_formats`
and is invalidated when it changes.

### Adding a country

Which format a country uses is a live SQL table
(`postal_code_country_formats`), not compiled in. Assigning a country to a
format that already exists needs no rebuild:

    SELECT add_country_format('DE', 'FR');   -- Germany: same plain 5-digit shape as FR/CZ
    SELECT 'DE-12345'::postal_code;          -- works immediately, no extension reinstall
    SELECT remove_country_format('DE');      -- undo -- new DE text stops parsing, but
                                              -- values already stored as DE are unaffected
                                              -- (decoding uses the format bits already in
                                              -- the value, never a fresh lookup)

`SELECT * FROM postal_code_formats` lists the formats a country can be assigned;
`add_country_format()` raises if you name one that isn't there. A country whose
codes can be described by a pattern needs no C at all -- see the next section. Only
a format a pattern cannot hold (the UK's area table, Canada's and Ireland's
encodings, which are compiled for their size and speed) needs real C (in
`postal_code_fmt.c` and a new `postal_code_fmt.h` tag) and a new extension version.

### Every country in the world

The extension ships with a format for every country and territory that has a postal
code system: 194 of the 250 ISO 3166-1 entries (the other 56 have no postal codes,
and the table says so). Eight formats are compiled (US, CA, FR, BR, CZ, LU, GB, IE);
the rest are patterns (below). The formats come from the real GeoNames data
(`"@GEONAMES".world`, 120 countries and 1.65 million codes, every one of which loads
except 21 French non-codes, one American Samoa ZIP filed under Samoa and the UK's `W1M`) and, for countries it doesn't cover, Wikipedia's list of
postal codes. Territories that use another country's system share its format
(`PR`, `GU`, `VI`... use the US one, `RE`, `GP`, `MQ`... the French).

    SELECT * FROM postal_code_world;                      -- every country, its format, where it came from
    SELECT * FROM postal_code_world WHERE basis = 'no postal codes';
    SELECT * FROM postal_code_world WHERE note IS NOT NULL;   -- the judgement calls

The UAE has no postal codes, but two schemes work like them: Abu Dhabi's 5-digit
district codes (`20000`, `23251`) and Dubai's Makani numbers, a 10-digit code for each
building written `NNNNN NNNNN` (all that GeoNames holds for the UAE). One format,
`NNNNN[ NNNNN]`, holds both, so any five digits pass, and the `postal_code_world` note
says what it is. Sharjah's PCS is not modelled, because there is nothing here yet to
say what its codes look like; PO Box numbers, which is what most UAE addresses give, are
not codes and are rejected.

Where the sources disagree or are doubtful (Egypt, Myanmar, Vietnam, Israel) the pattern
accepts both lengths, and that is recorded in `note`. Where a country's rules are narrower
than "so many digits", the pattern says so: Turkey's province `01` to `81` (and `99` for
the north of Cyprus), Monaco's `980NN`, the Faroes' `100` to `999`. Such
rules were adopted only after they rejected nothing in GeoNames (see `NARROWING.md`). The French territories keep the French format, because they use CEDEX
too. The table is generated by `tools/world_formats.py`; changing a built-in country is
a new pattern for it (old values keep reading as written), done through
`add_country_template()` like any other.

### Patterns: new countries without C

    SELECT add_country_template('XA', 'NN-NNN');                  -- a (made up) country
    SELECT add_country_template('XB', 'NNNN[ AA]');               -- 4 digits, optionally + 2 letters
    SELECT add_country_template('XC', 'NNN[-NNNN]');              -- 3 digits, optionally + 4
    SELECT add_country_template('XD', '/[1-9]\d{3}( [A-Z]{2})?/');   -- or a regular expression
    SELECT add_country_template('XE', '/([A-HJ-NP-Z]\d{4}([A-Z]{3})?|\d{4})/');  -- Argentina's three forms
    SELECT 'xa-00950'::postal_code;                               -- XA-00-950
    SELECT postal_code_pattern_size('/[1-9]\d{3}( [A-Z]{2})?/');  -- how many codes: 6093000

A country's codes are defined by a **pattern**: either a *template* -- `N` a digit, `A` a
letter, `X` either, a space or hyphen, `[ ... ]` around an optional part (they may nest) --
or a *regular expression* written between slashes. A regular expression is a bounded subset
of POSIX: literals, `\d`, classes (`[A-HJ-NP-Z]`, `[^0]`), `?`, `{n}`, `{n,m}`, groups
`( )` and `(?: )`, alternation `|`, and `^ $` at the ends. It has no `*`, `+`, `.` or
lookahead (a pattern must be finite) and is upper case (input is folded). It can say what
a template cannot: that a letter is never used (`[A-HJ-NP-Z]`), that a part is one of
several lengths, that a code starts with a fixed prefix, that `0000` is not a code.

The pattern defines the *whole set* of the country's codes, and a code is stored as its
rank in that set in text order. That gives, for any pattern and with nothing more to
configure: ordering is text ordering (`'PL-00-949' < 'PL-00-950'`, countries in ISO order),
validity is exact, no bits are wasted (a pattern with 3 billion codes needs 32), and
prefix ranges are exact: `lower_bound`/`upper_bound`/`postal_prefix` return real codes,
never a string the pattern would reject. `%` and index use, `outcode()`,
`is_valid_postal_code()`/`to_postal_code()`, the country lock and binary send/receive all work.
Separators (a space or hyphen the pattern has) may be left out of the input and are put
back, and letters are accepted in either case.

* **The country's own letters.** Some countries write the ISO code into the code itself:
  `VG1110` in the British Virgin Islands, `AD500`, `AZ 1000`, `HT6110`. The pattern leaves
  the letters out (`\d{4}`); on input they are accepted in front, must be the country's own
  (`XX1110` is not a Virgin Islands code), and are neither stored nor written back.
  `VG1110`, `VG-1110` and `VG-VG1110` are one value, written in the UPU form `VG-1110`.
* **The optional part is the incode.** A code that is a prefix of other codes
  (`NL-1012` of `NL-1012 AA`) is a value of its own and the `outcode()`; it sorts just
  before every value that extends it. A pattern with no such codes has no outcode, like
  France. When separators are left out, text such as `SW11` is read as the outcode `SW11`,
  not as `SW1 1`: for outcode and sector text the separator carries meaning. A full code
  is not ambiguous, because its last characters are the incode.
* **It must fit in 48 bits**: at most 2^48 (about 2.8 x 10^14) codes and 40 characters,
  and `add_country_template()` says so if it doesn't. `postal_code_pattern_check()` returns
  the regular expression a pattern means, and says what is wrong with one that is invalid.
* **A pattern is permanent.** Each pattern is a *language* of its country
  (`postal_code_languages`: country, version 1 to 51), and a stored value carries its
  version as its format, so it is always read the way it was written -- exactly as the
  built-in formats are. Rows cannot be updated or deleted. To change a country's rules,
  assign it a *new* pattern: it becomes the country's next version, new values follow it,
  and values already stored go on reading as they were written (and are different
  values -- `PL-00-950` under `NN-NNN` is not equal to `PL-00950` under `NNNNN`). Each
  country has 51 versions of its own; one country's changes use up nothing of another's.
  **A text dump reads values with the current pattern.** `pg_dump` writes `postal_code` as
  text, and a restore parses that text with the country's *current* pattern, so a value
  stored under an older version that the current one rejects (`XA-00-950` after `XA` moved
  to `NNNNN`) fails to load; one the current pattern also accepts is reloaded as a value
  of the current version. Change a country's pattern before loading data where you can,
  and check dump and restore after changing one that holds data.
* **Dump and restore.** Everything you add survives a dump and restore: the languages
  you add (`postal_code_languages`) and your country assignments
  (`postal_code_user_countries`) are dumped with the database. What ships with the
  extension (`postal_code_builtin_countries`, the built-in languages) is recreated by
  `CREATE EXTENSION` and is not. `postal_code_country_formats` is the view that merges the
  two (a user row wins; its `builtin` column says which each row is). Change assignments
  only through `add_country_format()`, `add_country_template()` and
  `remove_country_format()` -- removing a built-in assignment records that fact
  rather than deleting the shipped row.

  **One restore caveat.** The type's text input needs the country assignments and
  languages, and `pg_restore` orders table data by name, so a table that sorts before
  `postal_code_user_countries` (say `addr`) can be loaded first and fail with
  `"PL" is not a supported country code`. Restore the extension's own data first,
  by reordering the restore list (tested):

      pg_restore -l db.dump > all.list
      grep -E 'TABLE DATA public postal_code_(languages|user_countries) ' all.list > cfg.list
      awk -v cfg=cfg.list 'BEGIN{while((getline l < cfg)>0) c=c l "\n"}
          /TABLE DATA public postal_code_(languages|user_countries) /{next}
          {print} / EXTENSION - postcode( |$)/{printf "%s", c}' all.list > ordered.list
      createdb -T template0 newdb
      pg_restore -L ordered.list -d newdb db.dump

  (Values already *stored* never need any of this; it is only the text form read
  back from a plain dump.)

One consequence worth knowing: `postal_code_in`/`postal_code(text, text)`
are declared `STABLE`, not `IMMUTABLE`, precisely because their result can
change if a country's assignment changes -- so, correctly, PostgreSQL will
refuse to let you build a functional index over a `::postal_code` cast or
the two-argument constructor (`CREATE INDEX ... (('US-' || col)::postal_code)`
errors: "functions in index expression must be marked IMMUTABLE"). Indexing
the already-typed column itself (`CREATE INDEX ON t (pc)`) is unaffected --
that only compares stored values, never re-parses text.

See `postal_code.h`/`postal_code_fmt.h` for the bit layout and dispatch
design, `postal_code_country.c` for the country->format lookup, and
`sql/postal_code.sql`/`expected/postal_code.out` for the regression tests
(including a small, checked-in slice of real GEONAMES.world data).

Binary send/recv, a btree opclass, and full comparison operators are
provided the same as for `postcode`. Partial matching is the range support
and the `%` operator described above.


Validation
----------
The type was tested on about 117 million real postcodes from OpenStreetMap, GeoNames and Companies House
before release: 99.85% of OpenStreetMap's `addr:postcode` values parse, every parsed code round-trips
through text and binary, order and prefix ranges agree with plain text, and an upgrade from 1.3.5 and a
dump and restore were checked on 829,000 real UK postcodes. See `VALIDATION.md` for the method, the bugs
it found and the gaps that remain.

Credits
-------
Developed up to 1.3.0 by Dave Green at patchsoft.
Taken up for bug fixing and gap filing by John Burn of Impact Data Metrics. The bulk of the code is from David Green.
Claude AI was used to analyse and apply code fixes and generate tests

Bugs
----
Regression tests are provided using pg_regress via the installcheck target. Please raise issues on the githib site or PGXN