Direktori Kode Pos
📍 My ZIP
📥 Datasets ⚡ API 🛠️ Postal Tools

Postal Code Database Schema, CSV Fields & UPU S42 Validation Rules

Complete technical specification for the Postalcodes.info normalized data model, SQL table DDL, B-Tree indexing strategy, and international address formatting.

📦 OPEN-SOURCE DEVELOPER KIT (MIT / ODbL) · NODE.JS, TYPESCRIPT & PYTHON

postalcodes-upu-regex — Zero-Dependency 249-Country UPU S42 Validator

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.

// JavaScript / TypeScript (ESM & CJS)
import { validatePostalCode } from 'postalcodes-upu-regex';
validatePostalCode('GB', 'sw1a1aa');
// => { valid: true, normalized: 'SW1A 1AA' }
# Python 3.8+ (Zero Dependencies)
from postalcodes_upu_regex import validate_postal_code
validate_postal_code("BR", "01001000")
# => {'valid': True, 'normalized': '01001-000'}
📦 Download v1.0.0 (.tar.gz) 🗜️ Download v1.0.0 (.zip) 📄 249-Country UPU Regex JSON ⚙️ OpenAPI 3.1.0 Spec

1. Canonical 9-Column Postal Data Model

Field Type Example Description
country_codeCHAR(2)ES / US / DEISO 3166-1 alpha-2 country code.
postal_codeVARCHAR(20)28001 / SW1A 1AAFormatted postal code preserving leading zeros and spaces.
postal_code_keyVARCHAR(20)28001 / sw1a1aaNormalized lowercase alphanumeric key for fast B-Tree indexing.
place_nameVARCHAR(180)MadridOfficial locality, municipality, or delivery zone name.
admin_name1VARCHAR(120)Comunidad de MadridFirst-level administrative division (State, Autonomous Community, Region).
admin_name2VARCHAR(120)MadridSecond-level administrative division (Province, County, Department).
latitude / longitudeDECIMAL(9,6)40.4238, -3.6852WGS84 geographic centroid coordinates.

2. Production SQL Table & Index Definition (SQLite / PostgreSQL / MySQL)

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);

📑 Excel & CSV Import Guide: Preserving Leading Zeros (e.g. 01001, 08001)

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:

Step 1 · Data → From Text/CSV In Excel, open a blank workbook and click Data → Get Data → From Text/CSV (do not double-click the CSV file).
Step 2 · UTF-8 & Transform Data Select 65001: Unicode (UTF-8) as File Origin, click Transform Data (Power Query), and select the postal_code column.
Step 3 · Set Data Type to Text Change Data Type from Whole Number to Text (ABC) → Choose Replace Current → Click Close & Load.

🛡️ International Postal Code Format & Regex Validation Rules (UPU S42)

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 / 🇫🇷 FranceNNNNN (5 digits)^\d{5}$28001 / 10115 / 75001
🇳🇱 Netherlands (Postcode)NNNN AA^[1-9]\d{3}\s?[A-Z]{2}$1012 JS
🇧🇷 Brazil (CEP) / 🇵🇹 PortugalNNNNN-NNN / NNNN-NNN^\d{4,5}-?\d{3}$01001-000 / 1000-001
🇮🇳 India (PIN) / 🇨🇳 China / 🇳🇬 NigeriaNNNNNN (6 digits)^\d{6}$110001 / 100001
🇦🇪 UAE / Dubai (P.O. Box / Makani)No national postal codeCheckout fallback: 0000000000 / P.O. Box

Frequently Asked Questions: Postal Database Schema & Standards

Why should postal_code be stored as VARCHAR/TEXT instead of INTEGER?

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.

What coordinate reference system is used across the datasets?

All latitude and longitude coordinates are normalized to WGS84 (EPSG:4326) decimal degrees.