-- Named parts: part(), parts(), prefix_of(), the postal_code_parts view, and the ambiguity check. -- A pattern names a part of a code with (?...); a name never changes which codes are valid or how -- they are stored. See NAMED_PARTS.md. -- errors from inside the extension's own PL/pgSQL functions would otherwise record the line they came from \set SHOW_CONTEXT never -- the compiled formats: pieces of text, named as to_char names them (GB) SELECT code, part(code, 'area') AS area, part(code, 'district') AS district, part(code, 'sector') AS sector, part(code, 'walk') AS walk FROM (VALUES ('GB-SW1A 1AA'::postal_code), ('GB-EC4Y 0HQ'), ('GB-M1 1AA'), ('GB-W1A'), ('GB-LS24'), ('GG-GY1 1AA'), ('GB-BX5 5AT'), ('GB-GX11 1AA')) v(code); code | area | district | sector | walk -------------+------+----------+--------+------ GB-SW1A 1AA | SW | 1A | 1 | AA GB-EC4Y 0HQ | EC | 4Y | 0 | HQ GB-M1 1AA | M | 1 | 1 | AA GB-W1A | W | 1A | | GB-LS24 | LS | 24 | | GG-GY1 1AA | GY | 1 | 1 | AA GB-BX5 5AT | BX | 5 | 5 | AT GB-GX11 1AA | GX | 11 | 1 | AA (8 rows) SELECT parts('GB-SW1A 1AA'::postal_code) AS gb_full, parts('GB-SW1A'::postal_code) AS gb_outcode, parts('US-90210-1234'::postal_code) AS us_zip4, parts('US-90210'::postal_code) AS us_zip5, parts('CA-K1A 0B1'::postal_code) AS ca_full, parts('CA-K1A'::postal_code) AS ca_fsa, parts('IE-A65 F4E2'::postal_code) AS ie_full, parts('IE-D6W'::postal_code) AS ie_key, parts('BR-01310-100'::postal_code) AS br_full, parts('BR-01310'::postal_code) AS br_base; gb_full | gb_outcode | us_zip4 | us_zip5 | ca_full | ca_fsa | ie_full | ie_key | br_full | br_base ---------------------------------------------------------------+----------------------------------------------------------------+------------------------------------+----------------------------------+------------------------------+-----------------------------+---------------------------------------------+-------------------------------------------+--------------------------------------------------+------------------------------------------------- {"area": "SW", "walk": "AA", "sector": "1", "district": "1A"} | {"area": "SW", "walk": null, "sector": null, "district": "1A"} | {"zip5": "90210", "plus4": "1234"} | {"zip5": "90210", "plus4": null} | {"fsa": "K1A", "ldu": "0B1"} | {"fsa": "K1A", "ldu": null} | {"unique_id": "F4E2", "routing_key": "A65"} | {"unique_id": null, "routing_key": "D6W"} | {"area": "1310", "region": "0", "suffix": "100"} | {"area": "1310", "region": "0", "suffix": null} (1 row) -- Royal Mail's levels are everything up to and including a piece: prefix_of(), not part() SELECT code, name, prefix_of(code, name) = postal_prefix(expect) AS as_expected FROM (VALUES ('GB-SW1A 1AA'::postal_code, 'area', 'GB-SW'), ('GB-SW1A 1AA', 'district', 'GB-SW1A'), ('GB-SW1A 1AA', 'sector', 'GB-SW1A 1'), ('GB-SW1A 1AA', 'walk', 'GB-SW1A 1AA'), ('GB-EC4Y 0HQ', 'district', 'GB-EC4Y'), ('US-90210-1234', 'zip5', 'US-90210'), ('US-90210-1234', 'plus4', 'US-90210-1234'), ('CA-K1A 0B1', 'fsa', 'CA-K1A'), ('IE-A65 F4E2', 'routing_key', 'IE-A65')) v(code, name, expect); code | name | as_expected ---------------+-------------+------------- GB-SW1A 1AA | area | t GB-SW1A 1AA | district | t GB-SW1A 1AA | sector | t GB-SW1A 1AA | walk | t GB-EC4Y 0HQ | district | t US-90210-1234 | zip5 | t US-90210-1234 | plus4 | t CA-K1A 0B1 | fsa | t IE-A65 F4E2 | routing_key | t (9 rows) -- the district piece is not Royal Mail's district, which is why both functions exist SELECT part('GB-SW1A 1AA'::postal_code, 'district') AS piece, prefix_of('GB-SW1A 1AA'::postal_code, 'district') AS level, lower_bound('GB-SW1A') AS level_starts_at, outcode('GB-SW1A 1AA') AS same_as_outcode; piece | level | level_starts_at | same_as_outcode -------+-------------------+-----------------+----------------- 1A | [GB-SW1A,GB-SW1B) | GB-SW1A | GB-SW1A (1 row) -- a part the code does not have is NULL; so is anything given NULL SELECT part('GB-SW1A'::postal_code, 'sector') IS NULL AS no_sector, part('GB-SW1A'::postal_code, 'walk') IS NULL AS no_walk, prefix_of('GB-SW1A'::postal_code, 'walk') IS NULL AS no_walk_prefix, part('US-90210'::postal_code, 'plus4') IS NULL AS no_plus4, part(NULL::postal_code, 'area') IS NULL AS null_code, part('GB-SW1A 1AA'::postal_code, NULL) IS NULL AS null_name, parts(NULL::postal_code) IS NULL AS null_parts; no_sector | no_walk | no_walk_prefix | no_plus4 | null_code | null_name | null_parts -----------+---------+----------------+----------+-----------+-----------+------------ t | t | t | t | t | t | t (1 row) -- group and filter by a level CREATE TEMP TABLE addr (id serial, pc postal_code); INSERT INTO addr (pc) VALUES ('GB-SW1A 1AA'), ('GB-SW1A 2AA'), ('GB-SW1A 2BB'), ('GB-SW1V 1AA'), ('GB-SW1A'), ('GB-EC4Y 0HQ'), ('US-90210-1234'), ('US-90210'), ('US-10001'); SELECT prefix_of(pc, 'district') AS district, count(*) FROM addr WHERE pc::text LIKE 'GB-%' GROUP BY 1 ORDER BY 1; district | count -------------------+------- [GB-EC4Y,GB-EC5) | 1 [GB-SW1A,GB-SW1B) | 4 [GB-SW1V,GB-SW1W) | 1 (3 rows) SELECT pc FROM addr WHERE pc <@ prefix_of('GB-SW1A 1AA'::postal_code, 'sector') ORDER BY pc; pc ------------- GB-SW1A 1AA (1 row) SELECT pc FROM addr WHERE pc <@ prefix_of('GB-SW1A 2AA'::postal_code, 'district') ORDER BY pc; pc ------------- GB-SW1A GB-SW1A 1AA GB-SW1A 2AA GB-SW1A 2BB (4 rows) -- mistakes are errors, and say what there is SELECT part('GB-SW1A 1AA'::postal_code, 'postcode'); ERROR: postal codes of GB have no part called "postcode" HINT: the parts are: area, district, sector, walk SELECT part('GB-SW1A 1AA'::postal_code, 'District'); ERROR: postal codes of GB have no part called "District" HINT: the parts are: area, district, sector, walk SELECT part('FR-75001'::postal_code, 'department'); ERROR: postal codes of FR have no named parts HINT: see postal_code_parts for the countries that do; a pattern names parts with (?...) SELECT parts('FR-75001'::postal_code); ERROR: postal codes of FR have no named parts HINT: see postal_code_parts for the countries that do; a pattern names parts with (?...) SELECT prefix_of('PL-00-950'::postal_code, 'x'); ERROR: postal codes of PL have no part called "x" HINT: the parts are: prefix, suffix SELECT part('US-~'::postal_code, 'zip5'); part ------ (1 row) -- the UK type has the same four parts, read from its own fields (and unlike postal_code's they are IMMUTABLE) SELECT pc, part(pc, 'area') AS area, part(pc, 'district') AS district, part(pc, 'sector') AS sector, part(pc, 'walk') AS walk FROM (VALUES ('SW1A 1AA'::postcode), ('EC4Y 0HQ'), ('M1 1AA'), ('BX5 5AT'), ('GY1 1AA'), ('GX11 1AA')) v(pc); pc | area | district | sector | walk ----------+------+----------+--------+------ SW1A 1AA | SW | 1A | 1 | AA EC4Y 0HQ | EC | 4Y | 0 | HQ M1 1AA | M | 1 | 1 | AA BX5 5AT | BX | 5 | 5 | AT GY1 1AA | GY | 1 | 1 | AA GX11 1AA | GX | 11 | 1 | AA (6 rows) SELECT parts('SW1A 1AA'::postcode), parts('M1 1AA'::postcode); parts | parts ---------------------------------------------------------------+------------------------------------------------------------- {"area": "SW", "walk": "AA", "sector": "1", "district": "1A"} | {"area": "M", "walk": "AA", "sector": "1", "district": "1"} (1 row) SELECT pc, prefix_of(pc, 'area') AS area, prefix_of(pc, 'district') AS district, prefix_of(pc, 'sector') AS sector, prefix_of(pc, 'walk') AS walk FROM (VALUES ('SW1A 1AA'::postcode), ('EC4Y 0HQ'), ('M1 1AA')) v(pc); pc | area | district | sector | walk ----------+------+----------+--------+---------- SW1A 1AA | SW | SW1A | SW1A 1 | SW1A 1AA EC4Y 0HQ | EC | EC4Y | EC4Y 0 | EC4Y 0HQ M1 1AA | M | M1 | M1 1 | M1 1AA (3 rows) -- ... which is what to_char's letters were: every one of them is now spelled with a name SELECT bool_and(to_char(pc, 'A') = part(pc, 'area') AND to_char(pc, 'D') = part(pc, 'district') AND to_char(pc, 'S') = part(pc, 'sector') AND to_char(pc, 'W') = part(pc, 'walk') AND to_char(pc, 'AD') = prefix_of(pc, 'district') AND to_char(pc, 'AD S') = prefix_of(pc, 'sector') AND to_char(pc, 'AD SW') = prefix_of(pc, 'walk') AND pc::text = prefix_of(pc, 'walk')) AS to_char_replaced FROM (VALUES ('SW1A 1AA'::postcode), ('EC4Y 0HQ'), ('M1 1AA'), ('BX5 5AT'), ('GY1 1AA'), ('GX11 1AA'), ('W1A 0AX'), ('B33 8TH'), ('CR2 6XH'), ('DN55 1PT'), ('LS24 8AA'), ('ZE1 0AA')) v(pc); to_char_replaced ------------------ t (1 row) -- ... and it agrees with postal_code, where both can hold the code SELECT bool_and(part(pc, n) = part(('GB-' || pc::text)::postal_code, n) AND parts(pc) = parts(('GB-' || pc::text)::postal_code)) AS same_as_postal_code FROM (VALUES ('SW1A 1AA'::postcode), ('EC4Y 0HQ'), ('M1 1AA'), ('BX5 5AT'), ('GY1 1AA'), ('GX11 1AA'), ('LS24 8AA')) v(pc), (VALUES ('area'), ('district'), ('sector'), ('walk')) names(n); same_as_postal_code --------------------- t (1 row) -- find the postcodes in the same sector or district: the % operator takes the text prefix_of() gives SELECT pc FROM (VALUES ('SW1A 1AA'::postcode), ('SW1A 1AB'), ('SW1A 2AA'), ('SW1V 1AA'), ('EC4Y 0HQ')) v(pc) WHERE pc % prefix_of('SW1A 1AA'::postcode, 'sector') ORDER BY pc; pc ---------- SW1A 1AA SW1A 1AB (2 rows) SELECT pc FROM (VALUES ('SW1A 1AA'::postcode), ('SW1A 1AB'), ('SW1A 2AA'), ('SW1V 1AA'), ('EC4Y 0HQ')) v(pc) WHERE pc % prefix_of('SW1A 1AA'::postcode, 'district') ORDER BY pc; pc ---------- SW1A 1AA SW1A 1AB SW1A 2AA (3 rows) SELECT prefix_of(pc, 'district') AS district, count(*) FROM (VALUES ('SW1A 1AA'::postcode), ('SW1A 2AA'), ('SW1V 1AA'), ('EC4Y 0HQ')) v(pc) GROUP BY 1 ORDER BY 1; district | count ----------+------- EC4Y | 1 SW1A | 2 SW1V | 1 (3 rows) -- they can be indexed, which postal_code's cannot CREATE TEMP TABLE uk (pc postcode); INSERT INTO uk VALUES ('SW1A 1AA'), ('SW1A 2AA'), ('EC4Y 0HQ'); CREATE INDEX uk_area ON uk (part(pc, 'area')); SELECT part(pc, 'area') AS area, count(*) FROM uk GROUP BY 1 ORDER BY 1; area | count ------+------- EC | 1 SW | 2 (2 rows) -- a value that is not a valid postcode has no parts; a wrong part name is an error that says what there is SELECT part(range_upper('GX'), 'area') IS NULL AS bound_has_no_parts, to_char(range_upper('GX'), 'AD SW') AS what_to_char_gives; bound_has_no_parts | what_to_char_gives --------------------+-------------------- t | ?0 0AA (1 row) SELECT part('SW1A 1AA'::postcode, 'postcode'); ERROR: postcodes have no part called "postcode" HINT: the parts are: area, district, sector, walk SELECT part(NULL::postcode, 'area') IS NULL AS null_in_null_out; null_in_null_out ------------------ t (1 row) -- two types now have these functions, so a bare string literal cannot say which: cast it (a column needs nothing) SELECT part('SW1A 1AA', 'area'); ERROR: function part(unknown, unknown) is not unique LINE 1: SELECT part('SW1A 1AA', 'area'); ^ HINT: Could not choose a best candidate function. You might need to add explicit type casts. SELECT part('SW1A 1AA'::postcode, 'area'), part('GB-SW1A 1AA'::postal_code, 'area'); part | part ------+------ SW | SW (1 row) -- what each country's parts are SELECT iso2, format, ord, name FROM postal_code_parts WHERE iso2 IN ('GB', 'GG', 'US', 'CA', 'IE', 'BR', 'FR', 'PL') ORDER BY iso2, ord; iso2 | format | ord | name ------+------------+-----+------------- BR | BR | 1 | region BR | BR | 2 | area BR | BR | 3 | suffix CA | CA | 1 | fsa CA | CA | 2 | ldu GB | GB | 1 | area GB | GB | 2 | district GB | GB | 3 | sector GB | GB | 4 | walk GG | GB | 1 | area GG | GB | 2 | district GG | GB | 3 | sector GG | GB | 4 | walk IE | IE | 1 | routing_key IE | IE | 2 | unique_id PL | pattern v1 | 1 | prefix PL | pattern v1 | 2 | suffix US | US | 1 | zip5 US | US | 2 | plus4 (19 rows) -- the built-in patterns with real structure have parts; the ones that are one number have none SELECT count(DISTINCT iso2) AS countries_with_pattern_parts FROM postal_code_parts WHERE format = 'pattern v1'; countries_with_pattern_parts ------------------------------ 29 (1 row) SELECT iso2, string_agg(name, ', ' ORDER BY ord) AS parts FROM postal_code_parts WHERE format = 'pattern v1' GROUP BY iso2 ORDER BY iso2; iso2 | parts ------+------------------------ AR | province, number, face AS | zip5, plus4 BM | prefix, suffix CL | prefix, suffix CO | base, extension CR | base, extension ES | province, local FM | zip5, plus4 GU | zip5, plus4 IR | base, extension JP | prefix, suffix KY | island, number LB | base, extension MH | zip5, plus4 MP | zip5, plus4 MT | locality, number MZ | base, extension NL | digits, letters PL | prefix, suffix PR | zip5, plus4 PT | base, extension PW | zip5, plus4 SA | base, extension SO | prefix, suffix TR | province, local TW | base, extension UM | zip5, plus4 VE | base, extension VI | zip5, plus4 (29 rows) SELECT code, parts(code) FROM (VALUES ('PL-00-950'::postal_code), ('JP-100-0001'), ('CL-833-0000'), ('ES-28001'), ('TR-34000'), ('KY-1-1100'), ('MT-VLT 1117'), ('MT-VLT'), ('NL-1012 AB'), ('NL-1012'), ('PT-1000-001'), ('PT-1000'), ('AR-B1832GMR'), ('AR-B1832'), ('AR-1832'), ('CO-630001-025'), ('CO-630001'), ('TW-100-12'), ('BM-HM 12'), ('PR-00601-1234'), ('GU-96910'), ('VI-00802')) v(code); code | parts ---------------+---------------------------------------------------- PL-00-950 | {"prefix": "00", "suffix": "950"} JP-100-0001 | {"prefix": "100", "suffix": "0001"} CL-833-0000 | {"prefix": "833", "suffix": "0000"} ES-28001 | {"local": "001", "province": "28"} TR-34000 | {"local": "000", "province": "34"} KY-1-1100 | {"island": "1", "number": "1100"} MT-VLT 1117 | {"number": "1117", "locality": "VLT"} MT-VLT | {"number": null, "locality": "VLT"} NL-1012 AB | {"digits": "1012", "letters": "AB"} NL-1012 | {"digits": "1012", "letters": null} PT-1000-001 | {"base": "1000", "extension": "001"} PT-1000 | {"base": "1000", "extension": null} AR-B1832GMR | {"face": "GMR", "number": "1832", "province": "B"} AR-B1832 | {"face": null, "number": "1832", "province": "B"} AR-1832 | {"face": null, "number": null, "province": null} CO-630001-025 | {"base": "630001", "extension": "025"} CO-630001 | {"base": "630001", "extension": null} TW-100-12 | {"base": "100", "extension": "12"} BM-HM 12 | {"prefix": "HM", "suffix": "12"} PR-00601-1234 | {"zip5": "00601", "plus4": "1234"} GU-96910 | {"zip5": "96910", "plus4": null} VI-00802 | {"zip5": "00802", "plus4": null} (22 rows) SELECT prefix_of('ES-28001'::postal_code, 'province') = postal_prefix('ES-28') AS spain_province_level, prefix_of('NL-1012 AB'::postal_code, 'digits') = postal_prefix('NL-1012') AS dutch_digits_level, prefix_of('PL-00-950'::postal_code, 'prefix') = postal_prefix('PL-00') AS polish_prefix_level; spain_province_level | dutch_digits_level | polish_prefix_level ----------------------+--------------------+--------------------- t | t | t (1 row) -- naming never changed which codes a built-in language denotes ... SELECT count(*) AS builtin_languages_that_differ_from_their_source FROM postal_code_languages l WHERE builtin AND NOT postal_code_same_codes(postal_code_pattern_check(l.source), l.pattern); builtin_languages_that_differ_from_their_source ------------------------------------------------- 0 (1 row) -- a pattern of your own names its parts: a regular expression with (?...) SELECT add_country_template('QP', '/(?\d{2})-(?\d{3})(-(?[A-C]))?/'); add_country_template ---------------------- (1 row) SELECT parts('QP-12-345'::postal_code), parts('QP-12-345-B'::postal_code); parts | parts ------------------------------------------------+----------------------------------------------- {"extra": null, "major": "12", "minor": "345"} | {"extra": "B", "major": "12", "minor": "345"} (1 row) SELECT part('QP-12-345-B'::postal_code, 'extra') AS extra, part('QP-12-345'::postal_code, 'extra') IS NULL AS no_extra; extra | no_extra -------+---------- B | t (1 row) SELECT prefix_of('QP-12-345-B'::postal_code, 'minor') = postal_prefix('QP-12-345') AS minor_level; minor_level ------------- t (1 row) SELECT ord, name FROM postal_code_parts WHERE iso2 = 'QP' ORDER BY ord; ord | name -----+------- 1 | major 2 | minor 3 | extra (3 rows) -- nesting: the outer part includes its children; an alternation: only one branch's part is present SELECT add_country_template('QR', '/(?(?\d{2})(?\d{2})?)|(?[A-C]{3})/'); add_country_template ---------------------- (1 row) SELECT parts('QR-1234'::postal_code), parts('QR-12'::postal_code), parts('QR-ABC'::postal_code); parts | parts | parts ---------------------------------------------------------------+-------------------------------------------------------------+-------------------------------------------------------------- {"code": "1234", "word": null, "first": "12", "second": "34"} | {"code": "12", "word": null, "first": "12", "second": null} | {"code": null, "word": "ABC", "first": null, "second": null} (1 row) -- the names are labels: the same codes written or named differently relabel the language, they do not make a new version SELECT add_country_template('QP', '/(?\d{2})-(?\d{3})(-(?[A-C]))?/'); add_country_template ---------------------- (1 row) SELECT version, source FROM postal_code_languages WHERE iso2 = 'QP' ORDER BY version; version | source ---------+------------------------------------------ 1 | /(?\d{2})-(?\d{3})(-(?[A-C]))?/ (1 row) SELECT parts('QP-12-345-B'::postal_code) AS relabelled, 'QP-12-345-B'::postal_code = 'QP-12-345-B'::postal_code AS same_value; relabelled | same_value -----------------------------------+------------ {"a": "12", "b": "345", "c": "B"} | t (1 row) -- ... and they can go altogether, even though the pattern is written quite differently SELECT add_country_template('QP', '/\d{2}-\d{3}(-[A-C])?/'); add_country_template ---------------------- (1 row) SELECT version, source FROM postal_code_languages WHERE iso2 = 'QP' ORDER BY version; version | source ---------+------------------------ 1 | /\d{2}-\d{3}(-[A-C])?/ (1 row) SELECT count(*) AS parts_left FROM postal_code_parts WHERE iso2 = 'QP'; parts_left ------------ 0 (1 row) SELECT part('QP-12-345'::postal_code, 'a'); ERROR: postal codes of QP have no part called "a" HINT: the parts are: none SELECT add_country_template('QP', '/(?\d{2})-(?\d{3})(-(?[A-C]))?/'); add_country_template ---------------------- (1 row) SELECT version, source FROM postal_code_languages WHERE iso2 = 'QP' ORDER BY version; version | source ---------+------------------------------------------------------ 1 | /(?\d{2})-(?\d{3})(-(?[A-C]))?/ (1 row) -- ... but a different set of codes is a new language SELECT add_country_template('QP', '/(?\d{2})-(?\d{4})/'); add_country_template ---------------------- (1 row) SELECT version, source FROM postal_code_languages WHERE iso2 = 'QP' ORDER BY version; version | source ---------+------------------------------------------------------ 1 | /(?\d{2})-(?\d{3})(-(?[A-C]))?/ 2 | /(?\d{2})-(?\d{4})/ (2 rows) SELECT 'QP-12-3456'::postal_code = 'QP-12-3456'::postal_code AS version2_works, parts('QP-12-3456'::postal_code); version2_works | parts ----------------+-------------------------- t | {"a": "12", "b": "3456"} (1 row) -- a built-in language is never edited, and the same codes never make a new version of it SELECT add_country_template('DK', '/(?\d{4})/'); NOTICE: the built-in language of DK already denotes these codes and is kept as it is, with its own part names add_country_template ---------------------- (1 row) SELECT add_country_template('PL', 'NN-NNN'); -- exactly what PL was made from: nothing to do, nothing said add_country_template ---------------------- (1 row) SELECT add_country_template('PL', '/\d{2}-\d{3}/'); NOTICE: the built-in language of PL already denotes these codes and is kept as it is, with its own part names add_country_template ---------------------- (1 row) SELECT iso2, version, builtin FROM postal_code_languages WHERE iso2 IN ('DK', 'PL') ORDER BY iso2, version; iso2 | version | builtin ------+---------+--------- DK | 1 | t PL | 1 | t (2 rows) SELECT add_country_template('DK', '/(?\d{5})/'); add_country_template ---------------------- (1 row) SELECT iso2, version, builtin FROM postal_code_languages WHERE iso2 = 'DK' ORDER BY version; iso2 | version | builtin ------+---------+--------- DK | 1 | t DK | 2 | f (2 rows) -- a language still cannot change what it means; only be written another way UPDATE postal_code_languages SET pattern = '\d{5}' WHERE iso2 = 'QP' AND version = 1; ERROR: postal_code_languages rows are permanent: stored postal_code values are decoded with them HINT: to change a country's rules, add a new language with add_country_template(); a pattern can only be rewritten in place to one that denotes exactly the same codes, as when naming its parts UPDATE postal_code_languages SET pattern = '(?\d{2})-(?\d{3})(-[A-D])?' WHERE iso2 = 'QP' AND version = 1; ERROR: postal_code_languages rows are permanent: stored postal_code values are decoded with them HINT: to change a country's rules, add a new language with add_country_template(); a pattern can only be rewritten in place to one that denotes exactly the same codes, as when naming its parts UPDATE postal_code_languages SET source = 'x' WHERE iso2 = 'QP' AND version = 1; ERROR: postal_code_languages rows are permanent: stored postal_code values are decoded with them HINT: to change a country's rules, add a new language with add_country_template(); a pattern can only be rewritten in place to one that denotes exactly the same codes, as when naming its parts DELETE FROM postal_code_languages WHERE iso2 = 'QP'; ERROR: postal_code_languages rows are permanent: stored postal_code values are decoded with them HINT: to change a country's rules, add a new language with add_country_template(); a pattern can only be rewritten in place to one that denotes exactly the same codes, as when naming its parts UPDATE postal_code_languages SET pattern = '(?\d{2})-(?\d{4})' WHERE iso2 = 'QP' AND version = 2; SELECT pattern FROM postal_code_languages WHERE iso2 = 'QP' ORDER BY version; pattern ---------------------------------------------------- (?\d{2})-(?\d{3})(-(?[A-C]))? (?\d{2})-(?\d{4}) (2 rows) -- a pattern whose parts could split some code two ways is refused, with an example SELECT add_country_template('QS', '/(?\d{1,2})(?\d{1,2})/'); ERROR: the named parts of this pattern are ambiguous: the code "000" can be split into its parts in more than one way HINT: make the parts unambiguous, for example give each one a different set of characters or a fixed length SELECT add_country_template('QS', '/(?[A-C]{1,2})(?[A-C]?\d)/'); ERROR: the named parts of this pattern are ambiguous: the code "AA0" can be split into its parts in more than one way HINT: make the parts unambiguous, for example give each one a different set of characters or a fixed length -- ... also when there are too many codes to try them all SELECT postal_code_pattern_size('/\d{1,4}\d{1,4}/') > 10000000 AS more_than_tried_exhaustively; more_than_tried_exhaustively ------------------------------ t (1 row) SELECT add_country_template('QS', '/(?\d{1,4})(?\d{1,4})/'); ERROR: the named parts of this pattern are ambiguous: the code "000" can be split into its parts in more than one way HINT: make the parts unambiguous, for example give each one a different set of characters or a fixed length -- ... and a pattern that is fine is accepted, whatever its size SELECT add_country_template('QS', '/(?[A-Z]{1,2})(?\d[A-Z\d]?)( (?\d)(?[A-Z]{2}))?/'); add_country_template ---------------------- (1 row) SELECT postal_code_pattern_size('/[A-Z]{1,2}\d[A-Z\d]?( \d[A-Z]{2})?/') > 10000000 AS also_more_than_tried_exhaustively; also_more_than_tried_exhaustively ----------------------------------- t (1 row) SELECT parts('QS-SW1A 1AA'::postal_code); parts --------------------------------------------------------------- {"area": "SW", "walk": "AA", "sector": "1", "district": "1A"} (1 row) SELECT add_country_template('QS', '/(?\d{2})(?\d{1,2})/'); add_country_template ---------------------- (1 row) SELECT parts('QS-1234'::postal_code), parts('QS-123'::postal_code); parts | parts ------------------------+----------------------- {"a": "12", "b": "34"} | {"a": "12", "b": "3"} (1 row) -- parts that cannot work are refused when the pattern is defined (a repeat INSIDE a part is fine) SELECT add_country_template('QT', '/(?\d)(?\d)/'); ERROR: invalid postal code pattern "/(?\d)(?\d)/": the part name "a" is used twice SELECT add_country_template('QT', '/(?\d){2}/'); ERROR: invalid postal code pattern "/(?\d){2}/": a named part cannot be repeated (it would match more than once); only ? is allowed on it SELECT add_country_template('QT', '/(?\d{1,3})/'); add_country_template ---------------------- (1 row) SELECT add_country_template('QT', '/(?\d?)/'); ERROR: invalid postal code pattern "/(?\d?)/": the part "a" can match nothing; a named part must match at least one character SELECT add_country_template('QT', '/(?\d)/'); ERROR: invalid postal code pattern "/(?\d)/": a part name is a lower case letter followed by lower case letters, digits or _ SELECT add_country_template('QT', '/(?<=a)\d/'); ERROR: invalid postal code pattern "/(?<=a)\d/": lookbehind is not supported; a named part is written (?...) SELECT add_country_template('QT', '/(?!a)\d/'); ERROR: invalid postal code pattern "/(?!a)\d/": only (?: ) grouping and (? ) named parts are supported, not lookahead or other (?...) forms SELECT add_country_template('QT', '/(?\d)(?\d)(?" after the part name SELECT add_country_template('QT', '(?NN)-(?NNN)'); ERROR: invalid postal code pattern "(?NN)-(?NNN)": a template cannot name parts of a code; write a regular expression between slashes, e.g. /(?\d{2})-(?\d{3})/ HINT: a template (N digit, A letter, X either, [ ] optional: "NNNNN[-NNNN]") or a regular expression between slashes ("/\d{5}(-\d{4})?/") -- the helpers SELECT postal_code_pattern_part_names('(?\d)(?\d)\d') AS names, postal_code_pattern_part_names('\d{5}') AS none; names | none -------+------ {x,y} | {} (1 row) SELECT postal_code_same_codes('(?\d{2})-(?\d{3})', '\d\d-\d\d\d') AS written_differently, postal_code_same_codes('\d{2}|[A-C]', '[A-C]|\d{2}') AS order_of_alternatives, postal_code_same_codes('\d{2}-\d{3}', '\d{2}-\d{4}') AS a_digit_more, postal_code_same_codes('\d{2}(-\d{3})?', '\d{2}-\d{3}') AS optional_part, postal_code_same_codes('\d{2}', '[0-9]{2}') AS a_class; written_differently | order_of_alternatives | a_digit_more | optional_part | a_class ---------------------+-----------------------+--------------+---------------+--------- t | t | f | f | t (1 row) SELECT postal_code_parts_check('(?\d{2})(?\d)') IS NULL AS fine; fine ------ t (1 row) SELECT postal_code_parts_check('\d{1,2}\d{1,2}') IS NULL AS no_parts_nothing_to_check; no_parts_nothing_to_check --------------------------- t (1 row) -- to_char(postcode, text) still works, and says it is deprecated SELECT to_char('SW1A 1AA'::postcode, 'AD SW'); to_char ---------- SW1A 1AA (1 row) SELECT obj_description('to_char(postcode, text)'::regprocedure, 'pg_proc') LIKE 'DEPRECATED since 2.1:%' AS deprecated; deprecated ------------ t (1 row)