Data Types That Matter

Picking the right container

Choosing the wrong type is like storing soup in a colander. PostgreSQL has one of the richest type systems of any database — get the type right at table-creation time and a hundred future bugs disappear before they happen.

The 90% types

TEXT for all string columns. VARCHAR(n) only when you genuinely need a hard length limit.
INTEGER / BIGINT for whole numbers. Use BIGINT for any column that could ever exceed ~2.1 billion (foreign keys to large tables, IDs, byte counts).
NUMERIC(p, s) for money and any value where rounding matters. Never use FLOAT/REAL for money — floating-point math will quietly destroy you.
BOOLEAN for true/false. Don't use INTEGER 0/1 unless you have a very strong reason.
TIMESTAMPTZ for any "when did this happen" column. Always with timezone — never bare TIMESTAMP.
DATE for date-only data (birthdays, due dates, fiscal periods).
UUID for public-facing identifiers; uuidv7() in PG 18+ is time-sortable and index-friendly.
JSONB for variable-shape data; TEXT[] for tag lists; BYTEA for binary blobs.

The most common mistakes

Storing money as FLOAT. Storing timestamps as TEXT or as plain TIMESTAMP without timezone. Using VARCHAR(255) reflexively because some tutorial said to. Storing booleans as VARCHAR. Each of these is hours of debugging compounded across the lifetime of the codebase.

Code

Sane defaults for a typical product table·sql

CREATE TABLE products (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id   UUID NOT NULL DEFAULT gen_random_uuid(),
    sku         TEXT UNIQUE NOT NULL,
    name        TEXT NOT NULL,
    description TEXT,
    price       NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    weight_kg   NUMERIC(8,3),
    in_stock    BOOLEAN NOT NULL DEFAULT TRUE,
    tags        TEXT[] NOT NULL DEFAULT '{}',
    metadata    JSONB NOT NULL DEFAULT '{}'::jsonb,
    launched_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Money: why FLOAT lies·sql

SELECT 0.1::float8 + 0.2::float8;
--      ?column?
-- ----------------------
--   0.30000000000000004

SELECT 0.1::numeric + 0.2::numeric;
--  ?column?
-- ----------
--      0.3

Picking the right container

The 90% types

The most common mistakes

Code

External links

Exercise

Progress

Comments 0