2026-09-11 · 6 min read · Rahul Chitturi
- sql
- decimal
- money
A price can enter an API as 10.005, become a binary floating-point value, round in application code, bind to a DECIMAL column, and round again during an aggregate. Each step can be locally reasonable while the final invoice differs by a cent.
DECIMAL and NUMERIC provide exact base-10 storage within declared precision and scale, but expressions, casts, drivers, and business rules still determine where rounding occurs.
Trace one value through every representation
Record a harmless boundary value as source text, parsed application type, bound parameter type, database expression type, stored value, aggregate result, serialized JSON, and displayed text. Include the rounding rule at each transition.
- Distinguish precision from scale.
- Avoid binary floating point for exact monetary arithmetic.
- Do not infer currency scale from a generic decimal column.
- Check implicit casts in mixed numeric expressions.
Choose one business rounding boundary
Define whether the domain rounds per unit, line, tax component, invoice, or settlement and which tie-breaking mode applies. Use explicit casts and maintained decimal arithmetic so application and database calculations implement the same rule.
Preserve unrounded values only when the business contract requires them, and keep presentation formatting separate from accounting values.
Build an exact regression matrix
Use the SQL formatter to inspect casts, JSON formatter for decimal-string fixtures, diff for expected ledger outputs, and number-base converter to explain why binary fractions differ. Keep real financial and customer data out of browser tools.
Test positive and negative ties, zero, maximum precision, excess scale, multiplication, division, tax, discounts, sums, refunds, currency conversion, ORM round trips, exports, and every supported database driver.