Lesson 24: Data Types & Choosing the Right One

Picking the correct column type (INT vs BIGINT, VARCHAR vs TEXT, NUMERIC vs FLOAT) affects both correctness and storage efficiency.

Real-time example

Money is stored as NUMERIC(10,2), never FLOAT — floating point rounding errors on prices cause real accounting discrepancies.

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,      -- large auto-increment range
    amount NUMERIC(10,2) NOT NULL, -- exact decimal, no rounding drift
    status VARCHAR(20) NOT NULL,
    metadata JSONB                 -- flexible semi-structured data
);

What's happening

NUMERIC stores an exact decimal representation; FLOAT/DOUBLE approximate it in binary, which is why 0.1 + 0.2 isn't exactly 0.3 in float math.

Reach for JSONB only for genuinely variable/unstructured data — don't use it to avoid designing proper columns.