How to Store US Addresses in a Database: Schema Design That Survives Real Data

Published November 15, 2026 by the USA Address Generator editorial team · Database Architecture · 9 min read

Column types, lengths and constraints for US addresses: why ZIP is text, why the house number is not an integer, when to split components, and how to handle history and duplicates.


An address table looks like the easiest schema decision in a project, and it produces a steady trickle of production bugs: ZIP codes that lose their leading zero, cities that are cut off at twenty characters, orders whose delivery address changed after they shipped. Almost all of them come from a few decisions made in the first migration. This guide goes through those decisions.

Start from what you will do with the address

There are three different jobs an address can have, and they want different schemas.

  • Display and print it. You show it back to the user and put it on a label. You need the lines as the user typed them, in order.
  • Send it to another system. A payment processor, a carrier or a tax engine wants specific fields: line 1, line 2, city, state, postal code, country.
  • Analyse it. You want to group by state, compute distance or match duplicates. You need normalised values and probably coordinates.

Most applications need the first two and only sometimes the third. That argues for a simple, line-based schema with a few well-typed fields, and against the temptation to break every address into house number, pre-directional, street name, suffix and post-directional. Splitting is hard to get right, and wrong splits cannot be recovered if you did not keep the original.

A schema that works

CREATE TABLE addresses (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    recipient       VARCHAR(100),
    company         VARCHAR(100),
    line1           VARCHAR(100) NOT NULL,
    line2           VARCHAR(100),
    city            VARCHAR(60)  NOT NULL,
    state           CHAR(2)      NOT NULL REFERENCES us_states (code),
    postal_code     VARCHAR(10)  NOT NULL,
    country         CHAR(2)      NOT NULL DEFAULT 'US',
    latitude        NUMERIC(9,6),
    longitude       NUMERIC(9,6),
    verified_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT now(),
    CONSTRAINT postal_code_format
        CHECK (country <> 'US' OR postal_code ~ '^[0-9]{5}(-[0-9]{4})?$')
);

Each choice there is deliberate.

Postal code is text

A ZIP code is an identifier, not a quantity. Nobody adds two ZIP codes together. Stored as an integer, 02134 becomes 2134, and every ZIP in New England, New Jersey and Puerto Rico is corrupted. VARCHAR(10) holds five digits, a hyphen and four more. If you will ever store addresses outside the United States, postal codes contain letters and spaces, which settles the question.

State is a foreign key, not an enumeration of fifty

Reference a us_states table that contains every code USPS uses: the states, DC, the territories and the military codes AA, AE and AP. A check constraint with fifty values is the bug described in every article about military and territory addresses. A lookup table also gives you a place to hang the name, the time zones and whatever else you later need.

The house number is not a column

It is tempting to add street_number INTEGER. Real house numbers include 123-45, 12A, 123 1/2 and N6W23001. If you need the number separately, for example because your payment processor wants it for address verification, store it as text and keep line1 as well.

Lengths are generous

Storage for a longer VARCHAR costs nothing until it is used, and truncation costs a failed delivery. There is no single official maximum for a US address line. Pick limits that fit real data with room to spare, then check the limits of the systems you send addresses to: label formats and payment APIs are often stricter than your database, and the place to enforce their limits is validation with a clear message, not a silent cut.

Coordinates are NUMERIC

Six decimal places of a degree is roughly ten centimetres, which is more precision than any geocoder provides. NUMERIC(9,6) stores that exactly. If you will run distance queries in PostgreSQL, a PostGIS geography column is the better tool; keep it alongside the plain columns rather than instead of them.

Keep what the user typed

If you standardise addresses, converting "Suite" to STE and "Avenue" to AVE, keep the original input as well. Standardisation occasionally changes meaning, verification services occasionally return the wrong match, and when a customer says "that is not what I entered", you need to be able to see what they entered. Two approaches:

ALTER TABLE addresses
    ADD COLUMN entered JSONB,          -- exactly what was submitted
    ADD COLUMN standardised BOOLEAN NOT NULL DEFAULT false;

or a separate address_inputs table if you want a full audit trail. Either is fine; overwriting the input is not.

Addresses on orders are snapshots

The most damaging address bug is not a formatting bug. An order references the customer's address by ID; the customer later edits that address; the order history now shows a delivery address the parcel never went to, and an invoice that no longer matches what was issued.

An address attached to something that has happened must be immutable. Either copy the address fields onto the order row at the time of purchase, or treat address rows as append-only and create a new row on every edit. Customer-facing "saved addresses" can be edited freely, as long as nothing historical points at the editable copy.

Duplicates

Users create the same address many times with small differences: "St" and "Street", a trailing space, different capitalisation. A unique constraint on the raw columns will not catch these, and a strict one will reject legitimate cases, such as two people at one address. If you want to detect likely duplicates, compute a normalised key and index it without making it unique:

ALTER TABLE addresses ADD COLUMN match_key TEXT GENERATED ALWAYS AS (
    upper(regexp_replace(line1 || '|' || coalesce(line2, '') || '|' || left(postal_code, 5),
                         '[^A-Za-z0-9|]', '', 'g'))
) STORED;

CREATE INDEX addresses_match_key_idx ON addresses (match_key);

That folds case, spacing and punctuation. It will not equate "Street" with "ST"; for that you need to standardise first. Use the key to suggest "did you mean your existing address?", not to block the insert.

Indexes worth having

  • postal_code, or an expression index on left(postal_code, 5), for regional queries.
  • (state, city) if you filter or group by place.
  • The foreign key from whatever owns the address, which PostgreSQL does not index automatically.

Avoid indexing line1 with a plain B-tree for search. Address search wants trigram or full-text indexes, and usually wants them on a concatenated, normalised form.

Testing the schema

A schema is tested by trying to put awkward data into it. Load a few thousand generated records to check volume, sorting and index use; the address generator exports SQL insert statements for exactly this. Then insert the awkward shapes by hand, including a leading-zero ZIP, a ZIP+4, a military address, a Puerto Rico address with accents, a hyphenated house number and a 30-character city, and read each one back. The edge case checklist has a list to work from. Anything that does not come back identical to what went in is a bug you have found cheaply.