SQLite REAL, explained

REAL is SQLite’s floating-point number — fast, compact and almost exact. That “almost” is the whole story: here’s how REAL is stored, what it can represent, and when to reach for something else.

In one paragraph (ELI12)

A REAL is a number with a decimal point, like 3.14 or -0.5. It is stored the way a calculator stores it: in 8 bytes, very fast, and good to about 15–16 significant digits. The catch is that some simple decimals, like 0.1, can’t be written exactly in binary — so tiny rounding errors creep in.

How it’s stored

Serial type 7: an 8-byte big-endian IEEE-754 double. FLOAT, DOUBLE, DOUBLE PRECISION and REAL all mean the same thing — there’s no 4-byte float in SQLite.

One space-saving trick: in a REAL-affinity column, a value like 100.0 that has no fractional part may be stored as a small integer and turned back into a REAL when read. typeof() still says real.

// decoding serial type 7, as in sqlite-format.js
case 7: return new DataView(b.buffer, b.byteOffset + p, 8).getFloat64(0); // big-endian

Precision

A double has 53 bits of mantissa: about 15.95 decimal digits. Decimal fractions whose denominator isn’t a power of two — 0.1, 0.2, 0.3 — are stored as the nearest binary fraction:

SELECT 0.1 + 0.2 = 0.3, 0.1 + 0.2 - 0.3, printf('%!.20f', 0.1);
-- 0 | 5.55111512312578e-17 | 0.100000000000000005   ← equality fails by a hair

Compare with a tolerance (abs(a - b) < 1e-9) or round first (round(x, 2)). Integers up to 2⁵³ are exact.

Examples

CREATE TABLE m (v REAL);
INSERT INTO m VALUES (1), ('2.5'), ('3e2'), ('abc');
SELECT v, typeof(v) FROM m;
-- 1.0   | real      ← an integer becomes REAL
-- 2.5   | real      ← numeric text is converted
-- 300.0 | real
-- abc   | text      ← not a number: kept as TEXT

SELECT 7 / 2, 7 / 2.0;
-- 3 | 3.5                       ← one REAL operand makes REAL arithmetic

Money: don’t use REAL

Sum a column of prices like 19.99 and you’ll get 1234.5600000000002. Store money as an integer number of cents (price_cents INTEGER) and divide for display, or keep exact decimal strings in TEXT. DECIMAL(10,2) doesn’t help — see NUMERIC.

Limits

• Largest finite value ≈ 1.7976931348623157e308; smallest positive normal ≈ 2.2e-308.
• Overflowing literals become infinity: SELECT 1e999 → Inf. SQLite stores NaN as NULL.
• round(x, n) rounds half away from zero; the result is still a binary double.
• The viewer prints REALs in a separate colour and always with a decimal point, so 1.0 never reads as the integer 1.

Storage class vs. declared type vs. affinity

SQLite is dynamically typed: the type belongs to the value, not the column. Every value is one of five storage classes — NULL, INTEGER, REAL, TEXT, BLOB. A column’s declared type only sets its affinity: a preference SQLite applies when you store a value, by five rules checked in order:

Order matters: CHARINT matches rule 1 before rule 2 and is INTEGER; FLOATING POINT contains INT and is INTEGER too. The viewer implements exactly these rules:

function affinityOf(declType) {
  const t = String(declType || '').toUpperCase();
  if (t.includes('INT')) return 'INTEGER';
  if (t.includes('CHAR') || t.includes('CLOB') || t.includes('TEXT')) return 'TEXT';
  if (t.includes('BLOB') || t.trim() === '') return 'BLOB';
  if (t.includes('REAL') || t.includes('FLOA') || t.includes('DOUB')) return 'REAL';
  return 'NUMERIC';
}

The other types

Open any .sqlite, .db or .sqlite3 file in the SQLite viewer — every column header has an ELI12 button that explains its type and links here. How the viewer reads a 20 GB database without loading it: how to open a 20 GB SQLite database in the browser.