SQL Reference ยท Constraints and Data Types
SQL FLOAT, REAL and DOUBLE types
Reference for SQL FLOAT, REAL and DOUBLE PRECISION: approximate floating-point numbers, rounding errors, comparing safely, infinity and NaN, dialect notes.
REAL, DOUBLE PRECISION and FLOAT are approximate numeric types. They store numbers in binary floating point (IEEE 754), which covers a huge range of magnitudes quickly but cannot represent most decimal fractions exactly. REAL is usually 4 bytes (about 7 digits) and DOUBLE PRECISION is 8 bytes (about 15 to 16 digits).
Use them for measurements, scores, coordinates and statistics, where a tiny error is meaningless. Do not use them for money or any value that must add up to the exact cent. For that see DECIMAL.
CREATE TABLE measurements ( id INTEGER PRIMARY KEY, temperature REAL, distance_km DOUBLE PRECISION );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
REAL DOUBLE PRECISION FLOAT FLOAT(n) -- n = number of binary digits of precision
| Type | Usual meaning |
|---|---|
REAL |
Single precision, 4 bytes in most systems. |
DOUBLE PRECISION |
Double precision, 8 bytes. Written DOUBLE in MySQL. |
FLOAT |
In the standard, a floating type with an optional binary precision. In many systems it means double precision. |
Special values: IEEE numbers also have positive and negative infinity and NaN ("not a number"). Databases differ in whether they let you store or produce them.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | Every real is an 8-byte IEEE double, whatever the declared name (REAL, FLOAT, DOUBLE). Division by zero returns NULL. Overflow produces infinity, which is shown as Infinity. |
| PostgreSQL | real (4 bytes), double precision (8 bytes), and float(n) maps to one or the other. Supports NaN and Infinity; dividing by zero is an error. |
| MySQL | FLOAT is 4 bytes, DOUBLE is 8. The FLOAT(M, D) display syntax is deprecated. No NaN or infinity values. |
| SQL Server | real is float(24) and float (alias for float(53)) is 8 bytes. |
| Oracle | BINARY_FLOAT, BINARY_DOUBLE are IEEE types. FLOAT(n) is a decimal-based subtype of NUMBER. |
-- PostgreSQL SELECT 0.1::double precision + 0.2 = 0.3; -- false SELECT 'NaN'::double precision; -- MySQL SELECT 0.1E0 + 0.2E0 = 0.3E0; -- 0
Usage notes
- Never compare floats with
=. Test closeness instead:ABS(a - b) < 0.000001, or round both sides to the same number of digits. - Order of addition matters. The sum of a million floats can differ by a hair depending on the order rows are read, which makes aggregates slightly non-repeatable.
- Literals such as
0.1are exact decimals in PostgreSQL, MySQL and SQL Server, but doubles in SQLite. The same expression can give different answers across systems. - Show rounded values. Use
ROUND()or formatting for output, and keep full precision in storage. - Large integers lose digits. A double holds integers exactly only up to 2^53 (about 9 quadrillion). Use
BIGINTfor ids. - Aggregates:
AVG()of integers returns a float in SQLite, and a decimal in PostgreSQL and MySQL. SeeAVG(). NULLis notNaN.NULLis a missing value,NaNis a number that is undefined.