Skip to content
Tutorials Center
My learning

Topics

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.

SQL
CREATE TABLE measurements (
  id INTEGER PRIMARY KEY,
  temperature REAL,
  distance_km DOUBLE PRECISION
);

Try it

SQL Why 0.1 + 0.2 is not exactly 0.3

Press Run (or Ctrl + Enter) to see the result here.

SQL Summing prices as floating point ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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.1 are 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 BIGINT for ids.
  • Aggregates: AVG() of integers returns a float in SQLite, and a decimal in PostgreSQL and MySQL. See AVG().
  • NULL is not NaN. NULL is a missing value, NaN is a number that is undefined.