SQL, data modeling, data quality, EDA, visualization · insight-1048
Do money arithmetic in an exact numeric type and compare floats with a tolerance
- Evidence type
- documented fact
- Difficulty
- easy
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- finance, banking, aviation, retail
Summary: situation, action, reason, boundary
Situation
A reconciliation sums millions of fare amounts stored as double precision, and a total differs from the ledger by a few cents that move when the query plan changes.
Action
Store money as exact numeric (or integer minor units), do the sums in that type, and round once at the end with a stated rule. For legitimately floating measures compare with an absolute and relative tolerance, never with equality.
Reason
Binary floating point cannot represent most decimal fractions exactly, and addition order changes rounding.
Boundary
Ledgers, invoices, reconciliations and tests that assert totals match between systems.
Symptom or risk
Floating-point sums are order-dependent and values are stored inexactly, so equality tests on totals fail intermittently and tiny residuals look like data defects.
Exceptions
Scientific or model features where small relative error is acceptable and speed matters.
Trade-offs
Exact numeric types are slower than floating point, and mixing them silently casts back to float.
Pitfalls
Casting to numeric after the float sum, which preserves the error.
Lessons and concepts
Claims and their sources
PostgreSQL documents real and double precision as inexact types, warns that comparing floating-point values for equality may not behave as expected, and recommends numeric for monetary amounts at a cost in speed.