SQLite and fractional numbers

 sqlite which datatype for fractional numbers

Respuesta rápida

For fractional numbers in SQLite, the primary data type is REAL, which stores values as 8-byte IEEE 754 double-precision floating-point numbers.

• Precision: REAL guarantees up to 15 digits of precision.

• Limitation: Because it uses binary floating-point representation, exact decimal arithmetic is not supported; computations may result in minute precision errors (e.g., $$ exactly).

• Alternative for Exactness: For financial or accounting data requiring exact decimal precision, store values as INTEGER (e.g., cents) or use the DECIMAL extension (which stores numbers as strings for arbitrary precision).

• Type Affinity: Declaring a column as DECIMAL, NUMERIC, or FLOAT assigns it a NUMERIC or REAL affinity, but SQLite internally stores fractional values using the REAL storage class.

Can I relay on exactness for 2 decimal positions when interacting with sqlite through php?

Yes — for 2 decimal places you can safely rely on exactness, as long as you round the result.

Here's why:

• A double-precision float (what both SQLite REAL and PHP floats use) has ~15–17 significant digits. An error of 1e-14 is far below the 0.005 threshold needed to change a 2-decimal result.

• So after any arithmetic (addition, subtraction, multiplication, division), applying ROUND(x, 2) in SQL or round($x, 2) in PHP will always recover the exact 2-decimal answer.

Practical pattern:

// In SQL
SELECT ROUND(SUM(amount), 2) FROM orders;

// In PHP
$total = round($a + $b, 2);
echo number_format($total, 2, '.', ''); // "123.45"

The one caveat: if you perform many chained multiplications/divisions (e.g., compounding interest over thousands of iterations), errors can theoretically accumulate. For typical 2-decimal use cases (prices, invoices, totals), this is a non-issue.

If you want to be structurally exact (zero risk, ever), store as INTEGER cents (12345 = $123.45) and only convert to a decimal for display. But for 2 decimal places, REAL + ROUND is perfectly reliable.