Complete technical specification for the Postalcodes.info normalized data model, SQL table DDL, B-Tree indexing strategy, and international address formatting.
Validate, normalize, guard leading zeros, and handle no-postcode checkout countries (UAE, Hong Kong, Panama, Qatar) across all 249 ISO-3166-1 territories offline in < 0.01 ms — backed by our 1,931,198 verified records.
| Field | Type | Example | Description |
|---|---|---|---|
| country_code | CHAR(2) | ES / US / DE | ISO 3166-1 alpha-2 country code. |
| postal_code | VARCHAR(20) | 28001 / SW1A 1AA | Formatted postal code preserving leading zeros and spaces. |
| postal_code_key | VARCHAR(20) | 28001 / sw1a1aa | Normalized lowercase alphanumeric key for fast B-Tree indexing. |
| place_name | VARCHAR(180) | Madrid | Official locality, municipality, or delivery zone name. |
| admin_name1 | VARCHAR(120) | Comunidad de Madrid | First-level administrative division (State, Autonomous Community, Region). |
| admin_name2 | VARCHAR(120) | Madrid | Second-level administrative division (Province, County, Department). |
| latitude / longitude | DECIMAL(9,6) | 40.4238, -3.6852 | WGS84 geographic centroid coordinates. |
CREATE TABLE postal_codes (
country_code CHAR(2) NOT NULL,
postal_code VARCHAR(20) NOT NULL,
postal_code_key VARCHAR(20) NOT NULL,
place_name VARCHAR(180) NOT NULL,
admin_name1 VARCHAR(120),
admin_name2 VARCHAR(120),
admin_name3 VARCHAR(120),
latitude DECIMAL(9,6),
longitude DECIMAL(9,6)
);
CREATE INDEX idx_pc_country_postal_key ON postal_codes (country_code, postal_code_key);
CREATE INDEX idx_pc_country_lat_lon ON postal_codes (country_code, latitude, longitude);
Many postal systems (Northeast US 01001–09999, Spain 01001–09999, Germany 01067–09999, Italy 00100) begin with a leading zero, while Canada (M5V 3L9), the UK (SW1A 1AA) and the Netherlands (1012 JS) use alphanumeric spaces. Opening a CSV directly by double-clicking in Excel strips leading zeros by converting the column to General/Number. Follow these 3 steps to keep every code intact:
postal_code column.
| Country / System | Format Pattern | Regex Validation Rule | Example |
|---|---|---|---|
| 🇺🇸 United States (ZIP / ZIP+4) | NNNNN or NNNNN-NNNN | ^\d{5}(-\d{4})?$ | 10001 / 02138 |
| 🇬🇧 United Kingdom (Postcode) | A[A]N[A/N] NAA | ^[A-Z]{1,2}\d[A-Z\d]?\s?\d[A-Z]{2}$ | SW1A 1AA |
| 🇨🇦 Canada (FSA + LDU) | ANA NAN | ^[A-Z]\d[A-Z]\s?\d[A-Z]\d$ | M5V 3L9 |
| 🇪🇸 Spain / 🇩🇪 Germany / 🇫🇷 France | NNNNN (5 digits) | ^\d{5}$ | 28001 / 10115 / 75001 |
| 🇳🇱 Netherlands (Postcode) | NNNN AA | ^[1-9]\d{3}\s?[A-Z]{2}$ | 1012 JS |
| 🇧🇷 Brazil (CEP) / 🇵🇹 Portugal | NNNNN-NNN / NNNN-NNN | ^\d{4,5}-?\d{3}$ | 01001-000 / 1000-001 |
| 🇮🇳 India (PIN) / 🇨🇳 China / 🇳🇬 Nigeria | NNNNNN (6 digits) | ^\d{6}$ | 110001 / 100001 |
| 🇦🇪 UAE / Dubai (P.O. Box / Makani) | No national postal code | Checkout fallback: 00000 | 00000 / P.O. Box |
Many countries use leading zeros (such as 01001 in Spain or 02108 in Boston, USA) or alphanumeric formats (UK SW1A 1AA, Canada M5V 2T6, Netherlands 1012 JS). Storing postal codes as integers strips leading zeros and breaks alphanumeric codes.
All latitude and longitude coordinates are normalized to WGS84 (EPSG:4326) decimal degrees.