Floating-point types such as real and double precision store inexact approximations. They are therefore unsafe for exact monetary amounts, where rounding errors can produce incorrect balances or charges. section 3.6
The tempting percentage calculation
A literal calculation such as SELECT 99.99 * 0.25; returns 24.9975. Applying the same expression to a money column displays £25.00, but that presentation does not make the underlying arithmetic suitable for exact currency. Store monetary amounts as NUMERIC or DECIMAL instead, and keep the currency in an adjacent column. section 3.6
SELECT 99.99 * 0.25; ?column? ---------- 24.9975 (1 row)
Inspect the unrounded result of multiplying a monetary-looking value by a fraction. section 3.6
Treat floating-point types as a data-modeling choice rather than a harmless spelling variation:
- Use NUMERIC instead of MONEY for exact monetary values; PostgreSQL treats NUMERIC and DECIMAL equivalently. section 3.6
- During migrations, map source types by definition and behavior, not by matching names; improper mappings can lose precision or produce incorrect calculations. section 10.5
