INTEGER vs BIGINT vs NUMERIC vs DECIMAL vs REAL vs DOUBLE PRECISION — when to use each, especially for money.

The one rule that matters most: money is NUMERIC, never a float. Binary floating point can't represent 0.10 exactly, so REAL/DOUBLE PRECISION accumulate rounding error and will eventually report a cent that doesn't reconcile. Everything else is about picking the smallest exact integer type that fits.

The exact integer types

Prefer INTEGER for counts; prefer BIGINT for surrogate primary keys. A high-volume table can exhaust a 4-byte key (the Postgres "transaction-ID-style" overflow is a real production incident), and migrating a PK from int to bigint later is painful. Use GENERATED ALWAYS AS IDENTITY rather than the legacy serial.

NUMERIC / DECIMAL — exact, arbitrary precision

NUMERIC and DECIMAL are the same type (DECIMAL is an alias). NUMERIC(p,s) stores exactly p total digits with s after the decimal point — no rounding error, ever. This is the money type:

amount numeric(12,2)   -- up to 10 digits before the point, 2 after

The cost is speed: NUMERIC arithmetic is done in software, far slower than CPU integer/float math. That's a fine trade for money. Unbounded NUMERIC (no (p,s)) is allowed and stores any value exactly, but pin (p,s) for money so the column enforces "two decimal places."

REAL / DOUBLE PRECISION — fast, inexact

REAL (float4, ~6 significant digits) and DOUBLE PRECISION (float8, ~15 digits) are IEEE-754 binary floats. Fast, compact, and approximate. Use them for scientific/measurement data where you genuinely want floating point — sensor readings, ML features, geometric coordinates — never for currency.

SELECT 0.1::double precision * 3;  -- 0.30000000000000004  ← why not for money

Don't use the money type

PostgreSQL has a MONEY type, but it has a fixed fraction tied to a server locale setting and no currency awareness — it's widely considered a mistake. Use NUMERIC plus a separate currency column.