Types, Constraints & Database Objects

Numeric Types and Precision

Choose numeric types from the quantity’s mathematical domain: exact integers and fixed-scale decimals preserve money and counts, while binary floating point trades exact decimal representation for range and speed.

Integer types model exact whole quantities.
DECIMAL(p,s) is exact decimal arithmetic.
Binary floating point is approximate.
Money needs an explicit currency and rounding policy.
Arithmetic can widen or overflow.
Logical exactness is separate from physical encoding.

The classic money failure

Exact line totals in the commerce schema

CREATE TABLE currencies (
  currency_code CHAR(3) PRIMARY KEY,
  minor_units SMALLINT NOT NULL CHECK (minor_units BETWEEN 0 AND 6)
);

ALTER TABLE order_items
  ADD COLUMN currency_code CHAR(3) NOT NULL REFERENCES currencies(currency_code);

SELECT order_id, SUM(CAST(quantity AS DECIMAL(12,0)) * unit_price) AS order_total
FROM order_items GROUP BY order_id;

Precision and scale examples
Declaration	Largest positive value	Good fit
`DECIMAL(9,2)`	`9,999,999.99`	Moderate currency amount
`DECIMAL(19,4)`	`999,999,999,999,999.9999`	Money with sub-minor precision
`BIGINT` minor units	Implementation-defined signed 64-bit maximum	One fixed scale and checked arithmetic
`DOUBLE PRECISION`	Very wide, approximate	Measurements and statistical calculations

Adding approximate 0.1 three times need not compare equal to exact 0.3 because each value is rounded to a nearby binary fraction. DECIMAL(10,2) stores the decimal cents exactly, although multiplication and division still require an agreed result scale and rounding rule.