Floating-point cheat sheet for SQL
Floating-Point Types
The SQL standard defines three binary floating-point types:
REALhas implementation-dependent precision (usually maps to a hardware-supported type like IEEE 754 single or double precision)DOUBLE PRECISIONhas implementation-dependent precision which is greater thanREAL(usually maps to IEEE 754 double precision)FLOAT(N)has at leastNbinary digits of precision, with an implementation-dependent maximum forN
The exponent range for all three types is implementation-dependent as well.
Decimal Types
The standard defines two fixed-point decimal types:
NUMERIC(M,N)has exactlyMtotal digits,Nof them after the decimal pointDECIMAL(M,N)is the same asNUMERIC(M,N), except that it is allowed to have more thanMtotal digits
The maximum values of M and N are implementation-dependent. Vendors often implement the two types identically.
How to Round
The SQL standard defines no explicit rounding, but most vendors provide a ROUND() or TRUNC() function.
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.
Resources
- Official ISO SQL 2008 standard (non-free)
- SQL 92 draft (free)
- MySQL numeric types
- PostgreSQL Numeric Types
- MS SQL Server data types
© Published at floating-point-gui.de under the Creative Commons Attribution License (BY)
