SQL Reference ยท Constraints and Data Types
SQL DECIMAL and NUMERIC types
Reference for SQL DECIMAL and NUMERIC: exact fixed-point numbers for money, precision and scale, rounding, SQLite's behavior and dialect differences.
DECIMAL(p, s) and NUMERIC(p, s) store exact decimal numbers. The precision p is the total number of significant digits and the scale s is how many of them come after the decimal point. A DECIMAL(10, 2) column holds values such as 12345678.90, with exactly two decimals.
Because the values are exact, 0.1 + 0.2 is exactly 0.3, which is why these types are used for money and anything else where rounding errors must not creep in. In the standard, DECIMAL and NUMERIC are nearly the same; in most databases they behave identically.
CREATE TABLE invoices ( id INTEGER PRIMARY KEY, total DECIMAL(10, 2) NOT NULL );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
DECIMAL(precision, scale) NUMERIC(precision, scale) DECIMAL(precision) -- scale 0 DECIMAL -- implementation-defined defaults
| Part | Description |
|---|---|
precision |
Total number of significant digits stored, both sides of the point. |
scale |
Digits after the decimal point. Must not exceed the precision. Default 0. |
Example: DECIMAL(5, 2) holds -999.99 to 999.99. A value with more decimals is rounded to the scale, and a value with too many integer digits raises an overflow error in most systems.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | No decimal storage. DECIMAL and NUMERIC have "numeric affinity": values become integers or 64-bit floats, and precision and scale are ignored. Store money as integer cents. |
| PostgreSQL | numeric(p, s) is arbitrary precision (up to 131072 digits before the point), exact and slower than floats. decimal is a synonym. numeric without arguments holds anything. |
| MySQL | DECIMAL(p, s) with p up to 65 and s up to 30. Exact arithmetic, and NUMERIC is a synonym. Default is DECIMAL(10, 0). |
| SQL Server | decimal(p, s) with p up to 38; storage is 5 to 17 bytes. Also money and smallmoney types, which have 4 decimals and surprising rounding in division. |
| Oracle | NUMBER(p, s) is the native type. DECIMAL and NUMERIC map to it. A negative scale rounds to the left of the point. |
-- PostgreSQL SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric; -- true -- SQL Server: result scale of a division follows decimal rules SELECT CAST(10 AS DECIMAL(10, 2)) / 3; -- 3.333333
Usage notes
- Use
DECIMALfor money and measured quantities that need exactness, andFLOATfor scientific data where tiny errors are acceptable. - Pick the scale for the business need, not for display. Prices can need 4 decimals for unit costs, even if invoices show 2.
- Rounding rules differ. Most databases round halves away from zero in
ROUND(), but some (SQL Server'smoney, Oracle's banker-style options, floats near a half) can behave differently, so test the cases that matter. - Arithmetic widens results. Multiplying two decimals adds their scales, so intermediate results need room.
- Comparing floats with
=is unreliable. Use a decimal type, integer cents, or a tolerance. - Currency conversion and tax need rules your database does not know: define them explicitly and store the currency code next to the amount.
- Performance: decimals are slower than integers and floats but rarely the bottleneck.