Floating-point cheat sheet for SQL
The SQL standard defines three binary floating-point types:
REALhas implementation-dependant precision (usually maps to a hardware-supported type like IEEE 754 single or double precision)
DOUBLE PRECISIONhas implementation-dependant precision which is greater than
REAL(usually maps to IEEE 754 double precision)
FLOAT(N)has at least
Nbinary digits of precision, with an implementation-dependant maximum for
The exponent range for all three types is implementation-dependant as well.
The standard defines two fixed-point decimal types:
Nof them after the decimal point
DECIMAL(M,N)is the same as
NUMERIC(M,N), except that it is allowed to have more than
The maximum values of
M are implementation-dependant. Vendors often implement the two types identically.
How to Round
The SQL standard defines no explicit rounding, but most vendors provide a
However, it usually makes little sense to round within the database, since its job is storing data, while rounding is an aspect of displaying data, and should therefore be done by the code in the presentation layer.
- Official ISO SQL 2008 standard (non-free)
- SQL 92 draft (free)
- MySQL numeric types
- PostgreSQL Numeric Types
- MS SQL Server data types