Numeric Types

SQL splits numeric data into two families: exact types, which store the value precisely with no rounding, and approximate types, which trade some precision for a much wider range and more compact storage. Picking between them — and picking the right size within the exact family — is one of the first decisions you make for almost every table.

Integer types

Integers hold whole numbers with no fractional part. They come in several sizes so you can pick one that comfortably fits your data without wasting space.

Type	Typical size	Typical 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

Choosing an integer size

SQL

CREATE TABLE order_items (
    id          BIGINT PRIMARY KEY,   -- expect very many rows over time
    order_id    INTEGER NOT NULL,     -- millions of orders is plenty of range
    quantity    SMALLINT NOT NULL     -- nobody orders 32,768+ units at once
);

Exact decimal types: DECIMAL / NUMERIC

DECIMAL(precision, scale) and NUMERIC(precision, scale) are interchangeable in most dialects. Precision is the total number of significant digits; scale is how many of those digits sit after the decimal point. DECIMAL(10, 2) stores up to 10 digits total, 2 of them after the point — perfect for currency amounts like 12345678.90.

Exact precision for money

SQL

CREATE TABLE invoices (
    id     SERIAL PRIMARY KEY,
    amount DECIMAL(10, 2) NOT NULL  -- exact: 199.99 is stored as exactly 199.99
);

INSERT INTO invoices (amount) VALUES (199.99);
SELECT amount * 3 FROM invoices; -- 599.97, exactly

Warning

Never use a floating-point type (`FLOAT`, `REAL`, `DOUBLE PRECISION`) for money or any value that must add up exactly. Floating-point numbers are stored in binary and cannot represent most decimal fractions precisely, so repeated arithmetic accumulates small rounding errors — a classic bug is 0.1 + 0.2 not equaling exactly 0.3. For financial data, always use `DECIMAL`/`NUMERIC`, or store amounts as integer cents.

Approximate types: FLOAT, REAL, DOUBLE PRECISION

These store an approximation of the value using a fixed number of binary digits. They can represent an enormous range very compactly, which makes them a good fit for scientific measurements, sensor readings, or machine-learning features — anywhere small rounding error is acceptable and raw performance or range matters more than exactness.

Approximate values for measurements

SQL

CREATE TABLE sensor_readings (
    id          SERIAL PRIMARY KEY,
    temperature REAL,              -- single precision, ~6-7 significant digits
    pressure    DOUBLE PRECISION   -- double precision, ~15-17 significant digits
);

Auto-incrementing primary keys

Most tables need a column that generates a new unique integer for every row automatically. Every major dialect supports this, but the syntax differs.

Dialect	Syntax
PostgreSQL	SERIAL / BIGSERIAL, or GENERATED ALWAYS AS IDENTITY
MySQL	INT ... AUTO_INCREMENT
SQL Server	INT ... IDENTITY(1,1)

Auto-increment across dialects

SQL

-- PostgreSQL
CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255));

-- MySQL
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255));

-- SQL Server
CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY, email VARCHAR(255));

Modern PostgreSQL syntax

`SERIAL` is a PostgreSQL convenience that predates the SQL standard identity syntax. Newer PostgreSQL code increasingly prefers `id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY`, which behaves the same way but follows the ANSI SQL standard used by SQL Server and recent MySQL versions too.

Choosing the smallest adequate type

Estimate the realistic maximum value a column will ever hold, then pick the smallest type that comfortably covers it with room to grow.
A SMALLINT "age" column and a BIGINT "age" column behave identically in queries, but the BIGINT wastes 6 extra bytes per row — trivial for 100 rows, meaningful for 500 million.
For primary keys on very large or fast-growing tables, BIGINT is usually the safer default despite the extra bytes, since running out of INTEGER range on a live table is painful to fix.
Smaller, fixed-size types also make indexes smaller and comparisons cheaper, which compounds into real query performance gains at scale.

Tip

When a value is a proportion or a rate rather than a count of currency units, `NUMERIC` is still usually preferable to `FLOAT` if you ever plan to sum or compare it exactly — reserve floating-point types for genuinely approximate, high-range scientific data.