Numeric Types

PostgreSQL offers three broad families of numeric type: fixed-size integers for whole numbers, exact arbitrary-precision types for money and other values where rounding errors are unacceptable, and floating-point types for approximate values like scientific measurements. Picking the right family matters as much for correctness as for storage efficiency.

Integer types

The three integer types differ only in their storage size and the range of values they can hold. Pick the smallest one that comfortably covers your expected range — going bigger than you need wastes storage across millions of rows for no benefit.

Type	Storage	Range
`SMALLINT`	2 bytes	-32,768 to 32,767
`INTEGER` (`INT`)	4 bytes	-2,147,483,648 to 2,147,483,647
`BIGINT`	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Declaring integer columns

SQL

CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY,
    quantity     SMALLINT NOT NULL DEFAULT 0,
    total_views  BIGINT NOT NULL DEFAULT 0
);

INTEGER is the practical default for most whole-number columns — IDs, counts, quantities. Reach for BIGINT when a value could realistically exceed about two billion (a high-traffic view counter, a globally-shared sequence), and SMALLINT only when you know the range is genuinely tiny and storage is at a premium.

Exact numeric types: NUMERIC and DECIMAL

NUMERIC (and its exact synonym DECIMAL) stores numbers with user-specified precision and scale — NUMERIC(precision, scale) — and every digit you write is stored and retrieved exactly. There is no rounding introduced by the storage format itself.

Exact precision for money

SQL

CREATE TABLE invoices (
    invoice_id  INTEGER PRIMARY KEY,
    amount_due  NUMERIC(10, 2) NOT NULL  -- up to 10 digits total, 2 after the decimal
);

INSERT INTO invoices VALUES (1, 199.99);
INSERT INTO invoices VALUES (2, 0.10);

SELECT amount_due + amount_due FROM invoices WHERE invoice_id = 2;
-- 0.20, exactly — no floating-point surprises

Warning

Never store monetary values in `REAL` or `DOUBLE PRECISION`. Floating-point types use a binary approximation that cannot represent most decimal fractions exactly — `0.10 + 0.20` can come back as something like `0.30000000000000004` instead of `0.30`. For money, quantities of currency, or any value where “every cent matters,” always use `NUMERIC`/`DECIMAL`.

Approximate numeric types: REAL and DOUBLE PRECISION

REAL (4 bytes, ~6 decimal digits of precision) and DOUBLE PRECISION (8 bytes, ~15 decimal digits of precision) are IEEE 754 floating-point types. They're fast and compact, but approximate — perfectly fine for scientific measurements, sensor readings, or graphics coordinates where tiny rounding differences don't matter, but wrong for anything that needs exact decimal arithmetic.

Floating-point for scientific data

SQL

CREATE TABLE sensor_readings (
    reading_id     BIGSERIAL PRIMARY KEY,
    temperature_c  DOUBLE PRECISION NOT NULL,
    recorded_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

Auto-incrementing integers: a quick preview

You'll frequently see SERIAL and BIGSERIAL used for auto-incrementing primary key columns — they're shorthand built on top of INTEGER/BIGINT plus a sequence. They get a full page of their own (SERIAL & Identity Columns) alongside the modern GENERATED ... AS IDENTITY syntax that PostgreSQL now recommends instead.

Note

Choosing a numeric type is a trade-off between storage size, range, and exactness. As a rule of thumb: `INTEGER`/`BIGINT` for counts and IDs, `NUMERIC` for anything involving money or exact decimals, and `REAL`/`DOUBLE PRECISION` only for approximate scientific values.

SMALLINT / INTEGER / BIGINT are exact whole-number types that differ only in storage size and range.
NUMERIC(precision, scale) stores exact decimal values — the only correct choice for money.
REAL / DOUBLE PRECISION are fast but approximate floating-point types — never use them for currency.
SERIAL / BIGSERIAL are auto-incrementing shorthand covered in depth on their own page.