Store money as NUMERIC, never as a float
Floating-point rounding errors are small until they show up in a trial balance.
Anish KumarDatabases#postgresql#fintech#data-modeling
Floats store numbers in binary, and most decimal fractions can't be represented exactly.
0.1 + 0.2 is 0.30000000000000004. That error is tiny on one row, but financial reports sum
thousands of rows — and a trial balance that is off by one paisa still doesn't balance.
In PostgreSQL, use NUMERIC(precision, scale) for amounts:
amount NUMERIC(18, 2) NOT NULLA few rules that follow from this:
- Don't convert to a JS
numberfor math. Do sums in SQL, or use a decimal library on the server. - Send amounts as strings in JSON.
"1250.50"survives the trip;1250.5may not after arithmetic. - Round once, at the edge. Keep full precision in calculations and round only when displaying or posting.