Money amounts in a database: why DECIMAL and not FLOAT

One of the most common mistakes in database design is storing prices and amounts in a FLOAT or DOUBLE column. The problem is not visible at first: the values look right and the reports almost always balance. Then an invoice turns out to be one cent off, an equality check fails for no apparent reason, a bank reconciliation does not add up. The cause is the way computers represent numbers with decimals.

The floating-point problem

The FLOAT and DOUBLE types follow the IEEE 754 standard and represent numbers in base 2. Many fractions that are simple in base 10, such as 0.1 or 0.2, become recurring in base 2, just as 1/3 becomes 0.333… in base 10. The computer truncates them to a finite number of binary digits, and the stored value is a very close approximation, but not an exact one.

An example you can try in almost any language:

>>> 0.1 + 0.2
0.30000000000000004
>>> 0.1 + 0.2 == 0.3
False

For scientific calculations this precision is more than enough. For money it is not: amounts must be exact to the cent, sums must be repeatable and a total must equal the sum of its lines. Over thousands of operations, the small errors accumulate and produce visible differences.

The solution: DECIMAL or NUMERIC

The DECIMAL and NUMERIC types, equivalent in most databases, store numbers exactly in base 10. They are declared with two parameters: precision, the total number of digits, and scale, the digits after the decimal point.

CREATE TABLE invoice_lines (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  description TEXT NOT NULL,
  quantity    NUMERIC(10,3) NOT NULL,
  unit_price  NUMERIC(12,4) NOT NULL,
  vat_rate    NUMERIC(5,2)  NOT NULL,
  net_amount  NUMERIC(12,2) NOT NULL
);

NUMERIC(12,2) accepts numbers with up to 10 integer digits and 2 decimals. Sums are exact: 0.10 plus 0.20 is exactly 0.30.

The unit price may need a scale greater than 2. Many price lists, especially wholesale or for fuel and materials sold by weight, quote prices with three or four decimals. The line total, on the other hand, is rounded to the cent.

The alternative: integer cents

Another widespread technique is to store amounts as whole numbers of cents: 12.50 euros becomes 1250. Integers are exact, fast and supported everywhere, even in languages or formats that have no decimal type.

It does have some drawbacks:

• you must always remember to divide by 100 for display and multiply on input;
• not every currency has two decimals: the Japanese yen has none, some currencies have three;
• it does not suit unit prices with more decimals.

For a business database, NUMERIC is usually the clearest choice. Integer cents are convenient in interfaces between systems and in applications dealing with a single currency.

The database is not enough: the code matters too

Storing correctly is no use if the application reads the value and converts it to a float to do its calculations. Programming languages have dedicated decimal types: in Python the decimal module, in Java BigDecimal, in C# decimal.

from decimal import Decimal, ROUND_HALF_UP

net = Decimal("19.99") * 3
vat = (net * Decimal("0.22")).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)
print(net, vat)  # 59.97 13.19

Note that the value is created from a string, Decimal("19.99"), not from a number, Decimal(19.99): in the latter case it would inherit the binary approximation of the floating-point literal.

Rounding: decide on a rule

There are several rounding rules, and choosing the wrong one produces one-cent differences:

Many languages, including Python with its round function, use half-even rounding by default, whereas invoices usually apply commercial half-up rounding. The rule must be chosen, documented and applied in a single place in the code.

Line by line or on the total?

Calculating VAT on each line and then summing can give a result a few cents different from calculating it on the total net amount. Both approaches are used; what matters is that the system always follows the same method required by the rules the business applies, and that the stored total matches exactly the sum shown on the document.

Multiple currencies

If the system handles several currencies, the amount alone is not enough: it must be stored together with the currency code, for example using ISO 4217 codes such as EUR or USD. Exchange rates must be kept with the date they refer to, and the converted amount must be saved at the time of the transaction, not recalculated later with today's rate.

In short

• FLOAT and DOUBLE approximate decimals and should not be used for money.
• DECIMAL/NUMERIC are exact: choose the scale according to use, beyond two decimals for unit prices if needed.
• Integer cents are a valid alternative, especially with a single currency.
• Application code must also use decimal types, created from strings.
• The rounding rule and the VAT calculation method must be decided and applied consistently.