Why shouldn't I store money as FLOAT in a database?

Exact decimal types store numbers in base 10 with a fixed scale:

CREATE TABLE payments (
    id       bigint PRIMARY KEY,
    amount   numeric(19, 4) NOT NULL,    -- PostgreSQL / Oracle NUMBER(19,4)
    currency char(3)        NOT NULL     -- ISO 4217: 'USD', 'EUR', 'JPY'
);
-- MySQL/MariaDB/SQL Server: DECIMAL(19, 4)

• 0.1 + 0.2 = 0.3 exactly.
• (19, 4) is a common choice: plenty of digits for large totals, and 4 decimal places for intermediate results like unit prices, tax and FX rates. Round to the currency's minor unit (usually 2) when you charge or display, not at every step.
• Arithmetic is a little slower than floats, which doesn't matter at all for business data.

Option 2: integers in minor units

Store cents (or the currency's smallest unit) in a bigint:

amount_minor bigint NOT NULL,   -- 1999 = $19.99
currency     char(3) NOT NULL

This is exact, fast and portable, and it's how many payment APIs work (Stripe amounts are integers in the smallest currency unit).

• Currencies have different minor units: JPY has 0 decimals, USD and EUR have 2, KWD and BHD have 3. Store the currency and look up its exponent; never assume 100.
• Fractional cents (per-unit pricing, interest) need either more precision (for example millicents) or a DECIMAL for those calculations.

Things to avoid

• PostgreSQL's money type: its formatting and input depend on the lc_monetary locale setting, it has no currency attached, and it's easy to misuse. The PostgreSQL wiki's "Don't Do This" page recommends numeric instead.
• SQL Server's money/smallmoney: fixed at 4 decimal places, and intermediate results in multiplication and division lose precision. decimal(19,4) behaves more predictably.
• Storing a single amount without its currency. "100" means nothing without "USD" or "JPY".
• Converting through floats in the application. An exact column doesn't help if JavaScript turns it into a Number. Use decimal libraries (decimal.js, Python's Decimal, Java's BigDecimal, C#'s decimal) or integer minor units end to end, and make sure the database driver returns decimals as strings or decimal objects, not floats.

Rounding is a business rule

Decide and document when you round (per line item or on the total) and how (half-up, or banker's rounding, half-to-even, which many accounting systems use). Implement it in one place. Most "off by one cent" disputes are really about rounding rules, not storage.

Checklist

1. Never store money in FLOAT/DOUBLE/REAL: 0.1 isn't exact in binary.
2. Use DECIMAL/NUMERIC (for example (19,4)) or integer minor units, and always store the currency.
3. Avoid money types, and keep amounts exact through the application layer too.
4. Define rounding rules explicitly, and round only when charging or displaying.

How do I insert a row only if it doesn't exist, without race conditions?

Select-then-insert breaks under concurrency. Use a unique constraint plus one atomic statement: ON CONFLICT, ON DUPLICATE KEY UPDATE, or UPDLOCK/SERIALIZABLE in SQL Server.

#concurrency#schema-design#transactions