skip to content

Why is FLOAT the wrong type for a money column, and what should you use instead?

level: juniorimportance: must knowfreq 82%

answer

  1. Think about base two versus base ten
  2. Cents are not exact binary fractions
  3. Small errors add up over many rows
  4. You need a type that stores digits
  5. Declare precision and scale explicitly

basics

~20 s

FLOAT and REAL store binary approximations, so a value such as 0.10 is never held exactly and the error accumulates across sums and multiplications. Money needs an exact type: DECIMAL/NUMERIC with a declared precision and scale.

solid answer

~40 s

`REAL`, `DOUBLE PRECISION` and `FLOAT` are *approximate* numeric types: they hold a binary fraction, and most decimal fractions — 0.10, 0.20, 0.07 — have no finite binary representation. Every stored amount is therefore slightly wrong, and the error accumulates when you sum millions of rows or multiply by a tax rate. The visible symptoms are a total that is a cent away from the hand-added figure, and predicates like `WHERE balance = 0` that miss a row displaying as 0.00. The fix is an *exact* numeric type: `DECIMAL(p, s)` (`NUMERIC(p, s)` is the same thing in practice), which stores base-10 digits and does exact decimal arithmetic. Declare the scale you actually need, e.g. `DECIMAL(12,2)` for ordinary cents. Approximate types are still right for measurements and statistics, where a relative error around 10^-15 is irrelevant.

code

sql · 11 lines
sql
-- Wrong: approximate binary storage for a counted quantity
CREATE TABLE payment_bad (
  payment_id BIGINT,
  amount     DOUBLE PRECISION NOT NULL
);

-- Right: exact base-10 storage, cents pinned in the schema
CREATE TABLE payment (
  payment_id BIGINT,
  amount     DECIMAL(12,2) NOT NULL
);

go deeper

for a junior

Be ready to say the sentence outright: FLOAT is approximate, money is exact, use DECIMAL with an explicit precision and scale. Knowing the 0.1 + 0.2 example is enough at this level.

for a middle

Explain the mechanism — binary mantissa, non-representable decimal fractions, error accumulating over sums — and show the DECIMAL(p,s) declaration you would write, including why you pin the scale rather than leaving it open.

for a senior

Be able to diagnose it in a live system: totals that disagree between two reports, reconciliation equality checks failing on near-zero balances, and the migration plan to move an existing FLOAT column to DECIMAL without corrupting the values already stored.

for a principal

Own the convention across services: one declared representation for monetary amounts, agreed rounding points in multi-step calculations, and the discipline that data leaving the database as JSON or a float in an API is where exactness is silently lost again.

## Two families of numeric type Standard SQL divides numbers into **exact numeric** types (`SMALLINT`, `INTEGER`, `BIGINT`, `DECIMAL(p,s)`, `NUMERIC(p,s)`) and **approximate numeric** types (`REAL`, `DOUBLE PRECISION`, `FLOAT(p)`). The difference is not storage size or speed — it is whether the value you wrote is the value that comes back. An exact numeric type stores decimal digits. `DECIMAL(9,2)` holds nine significant decimal digits, two of them after the decimal point, and arithmetic on it is defined in base 10. An approximate type stores a sign, a binary mantissa and a binary exponent, in the layout the CPU's floating-point unit uses. ## Why binary cannot hold 0.10 A binary fraction is a sum of 1/2, 1/4, 1/8 and so on. Only fractions whose denominator is a power of two are representable exactly. One tenth is not: in binary it is an infinitely repeating expansion, so the hardware keeps the closest representable value and drops the rest. `0.1 + 0.2` in double precision is very slightly greater than `0.3`, which is why the equality test fails even though both sides print as `0.3` at two decimals. The error per value is tiny — around one part in 10^16 for double precision. What makes it a defect for money is that money is *counted*, not measured: a ledger is expected to balance to the cent, and small errors both accumulate over many rows and get amplified by multiplication. ## What goes wrong in practice - **Totals drift.** `SUM(amount)` over a large table differs from the sum computed elsewhere, and the difference changes when rows are added in a different order, because floating-point addition is not associative. - **Equality fails.** `WHERE balance = 0` skips an account whose stored balance is 1e-17 rather than zero. Reconciliation jobs written as equality checks report phantom mismatches. - **Rounding is unstable.** Rounding 2.675 to two places may produce 2.67 rather than 2.68, because the stored value is a hair below 2.675. - **Displaying two decimals hides nothing.** The stored value is still approximate; formatting on the way out only postpones the discrepancy to the next comparison or the next sum. ## What to use instead Declare the column `DECIMAL(p, s)` with an explicit precision and scale: `amount DECIMAL(12,2) NOT NULL`. Precision `p` is the total count of significant digits; scale `s` is how many of them sit to the right of the decimal point. `DECIMAL(12,2)` therefore stores values up to 9,999,999,999.99, exactly, and adding, subtracting and multiplying such values is exact. A second sound option is to store the amount as an integer count of minor units — cents as `BIGINT`. It is exact by construction and common in payment systems, but it pushes the scale into application convention: the column says `1999` and only surrounding code knows whether that is cents, tenths of a cent, or yen. `DECIMAL` keeps the scale in the schema, which is usually the better trade. What you must not do is store money as text. `VARCHAR` preserves the digits but forbids arithmetic, comparison and ordering without a cast, and admits values the column should never hold. ## When approximate types are correct `DOUBLE PRECISION` is the right type for sensor readings, distances, ratios, scientific measurements and statistical intermediates — quantities that are inherently inexact, where the relative error is far below the measurement error and where exact equality is never the right test anyway. The rule of thumb: if the quantity is *counted* in discrete units, use an exact type; if it is *measured*, an approximate type is fine. ## Portability notes `DECIMAL` and `NUMERIC` are both in the standard and are treated as synonyms by most engines; the standard's fine distinction is that `NUMERIC` must give exactly the requested precision while `DECIMAL` may give at least that. Engines differ in how they name the approximate types and in what `FLOAT(p)` means, so prefer `DOUBLE PRECISION` or `REAL` when you genuinely want approximate storage. Some engines also offer a dedicated money type; it is convenient but non-portable and locks in a fixed scale.

  • If DECIMAL is exact, why does anyone still use DOUBLE PRECISION?
    Because most quantities are measured, not counted. Sensor readings, distances, ratios and statistical intermediates are inexact to begin with, and approximate types give a wide dynamic range with fast hardware arithmetic. The trade only becomes a defect when the value is a count of discrete units — money, inventory, votes — where exactness and stable equality matter more than range.
  • Some systems store money as an integer number of cents instead. What does that buy and cost?
    It is exact by construction and needs only BIGINT arithmetic, which is why payment systems often do it. The cost is that the scale lives in application convention rather than the schema: the column shows 1999 and nothing in the DDL says whether that means cents, tenths of a cent, or a currency with no minor unit. DECIMAL keeps that fact declared.
  • Would rounding to two decimals on the way out fix a FLOAT money column?
    No. Rounding at display time hides the drift from the human but leaves it in the stored data, so sums, equality predicates, reconciliation jobs and every downstream export still see the approximate values. It also makes rounding itself unstable, since a value stored just below a .xx5 boundary rounds down. Fix the column type, then backfill.

Binary floating point is a ruler marked only in halves, quarters and eighths: you can get very close to one tenth of an inch, but never land on it exactly.

saying these in an interview costs you the question

  • Says DOUBLE PRECISION is fine because it holds fifteen digits
  • Claims formatting to two decimals on output fixes stored drift
  • Thinks DECIMAL and FLOAT differ only in storage size
  • Compares floating-point amounts with equality and expects it to work
  • Suggests storing money in VARCHAR to keep the exact text

context

resolving links…