Every database has to take the values you write in code, like integers, strings, and decimals, and encode them into binary before writing them to disk. Most of the time, that encoding is invisible. But with money, the wrong encoding can quietly corrupt your data in ways that no query or application logic will catch.
If you have worked with money in a database, you have probably heard the advice that you should not use floating-point types. That advice is solid, but it is usually given without explaining what is actually going wrong at the binary level. And without that understanding, it is hard to evaluate your options and pick the right storage type for your application.
In this article, we will start by showing how a floating-point column can silently return the wrong total. Then we will go a level deeper into how decimal fractions are represented in binary, and why that makes exact storage impossible in a fixed number of bits. From there, we will walk through the safer alternatives and the tradeoffs between them so you can make an informed choice. The examples use PostgreSQL, but the types have equivalents in most databases, so the ideas apply anywhere.
The Problem With Storing Money With Floating-Point Types
Let us say we are building a simple wallet system. Since money can have decimal places, it may feel natural to store the amount with a floating-point type.
In PostgreSQL, one floating-point type we can use is double precision.
CREATE DATABASE ledger;
Now create a table for the wallet transactions.
CREATE TABLE wallet_transactions (
id serial PRIMARY KEY,
amount double precision NOT NULL
);
Let us insert 0.1 into the table 100 times.
INSERT INTO wallet_transactions (amount)
SELECT 0.1
FROM generate_series(1, 100);
If we add 0.1 together 100 times, the result should be 10.
0.1 × 100 = 10
Now let us sum the values in the table.
SELECT SUM(amount) AS total_amount
FROM wallet_transactions;
With double precision, this will happen:
total_amount
-------------------
9.99999999999998
We inserted 0.1 100 times, so we expected the result to be exactly 10. But because double precision stores approximate floating-point values, PostgreSQL returns a value that is only close to 10. To understand why this happens, we need to look at how decimal fractions are represented in binary.
Decimal Fractions in Binary
By the end of this section, you will be in the first group
A decimal fraction is a number like 0.1, 0.2, or 10.99. These numbers look simple in base 10, but the computer has to represent them in base 2, where the only available digits are 0 and 1.
For whole numbers, converting to binary is straightforward. A number like 13 can be represented exactly as 1101 in binary, and as long as it fits within the available bits, nothing is lost.
Fractions are different. To convert a decimal fraction to binary, we multiply by 2 repeatedly. Each time, the whole number part of the result becomes the next binary digit, and we continue with the remaining fraction.
Let us try this with 0.1.
0.1 × 2 = 0.2 -> digit 0, keep 0.2
0.2 × 2 = 0.4 -> digit 0, keep 0.4
0.4 × 2 = 0.8 -> digit 0, keep 0.8
0.8 × 2 = 1.6 -> digit 1, keep 0.6
0.6 × 2 = 1.2 -> digit 1, keep 0.2
In the last line, we get back to 0.2, which means the pattern will keep repeating. The 0011 part repeats every time, and the binary form of 0.1 becomes 0.0001100110011001100110011...
This is where the problem starts. 0.1 does not end cleanly in binary. To represent it exactly, the computer would need an infinite number of binary digits.
But a computer cannot store infinite digits. A floating-point value has a fixed amount of space. For example, a 64-bit float has only 64 bits available, so the computer stores the closest binary value it can fit into that space. That means the value stored is not exactly 0.1. It is only very close to it, something like 0.10000000000000000555... and that tiny difference is where floating-point precision errors come from.
What Should We Use Instead?
Now that we know why floating-point types break, let us look at the types that do not.
In PostgreSQL, the common options are money, integer, bigint, and numeric.
The Monetary Type
The money type stores a currency amount with fixed fractional precision. It may look like the natural choice for money because the name says exactly that.
CREATE TABLE payments (
id serial PRIMARY KEY,
amount money NOT NULL
);
The problem is that its behavior depends on a locale setting in PostgreSQL called lc_monetary. This setting controls how the database formats currency values, including the currency symbol, the decimal separator, and the position of the sign. Different servers or environments can have different locale settings, so the same stored value might be displayed as $10.99 on one server and 10,99 € on another. That kind of inconsistency can cause issues when moving data between environments or when different services read from the same database.
Storing Money as Whole Numbers
A better approach is to store money in the smallest unit of the currency as an integer. This keeps the stored value as a whole number instead of a floating-point value.
So instead of storing 10.99 dollars, you store 1099 cents
Integer
integer stores whole numbers and uses 4 bytes of storage. It is compact, fast, and exact, so it can work well when the values are small enough for its range.
CREATE TABLE payments (
id serial PRIMARY KEY,
amount_cents integer NOT NULL
);
This works because 1099 is stored as an exact whole number. There is no .99 for the database to approximate. The application can divide by 100 when it needs to display the value as dollars.
The main thing to watch is range. integer is efficient, but it can become too small if the system stores large amounts, long-running balances, or totals that keep growing.
Bigint
bigint is very similar to integer. It also stores whole numbers, but the main difference is that it can store a much larger range of values. The extra range comes with extra storage, because bigint uses 8 bytes instead of 4 bytes.
CREATE TABLE payments (
id serial PRIMARY KEY,
amount_cents bigint NOT NULL
);
To compare this with the earlier float example, let us store 0.1 dollars as 10 cents.
INSERT INTO payments (amount_cents)
SELECT 10
FROM generate_series(1, 100);
Now when we sum the values, the result is exact.
SELECT SUM(amount_cents) AS total_amount_cents
FROM payments;
total_amount_cents
--------------------
1000
1000 cents is exactly 10 dollars. This is the same exact result we would get with the integer type above, because both integer and bigint store the value as a whole number.
The reason to choose bigint is when the amount can grow beyond what integer can safely hold. If the values will always stay within the integer range, integer is more compact. If the values can grow over time, bigint gives you a larger range without changing the storage approach.
Numeric
If you want to store exact decimal values without converting to the smallest currency unit first, numeric is the type to use. It is also called decimal, and it lets you store values like 10.99 directly without the floating-point approximation problem.
It can also store very large numbers with decimal places.
9876543210.12345
So unlike integer and bigint, we do not have to convert the amount into the smallest currency unit first.
CREATE TABLE payments (
id serial PRIMARY KEY,
amount numeric NOT NULL
);
When numeric is used like this, the column is not forced into a fixed number of digits or decimal places. It can store very large decimal values, up to the database’s internal limit.
Under the hood, numeric is not stored like double precision. It uses a variable-length decimal representation instead of binary floating-point approximation. That is why values like 0.1, 10.99, and 25.50 can be stored exactly.
If we want to control the size of the value, we can define precision and scale.
CREATE TABLE payments (
id serial PRIMARY KEY,
amount numeric(5, 2) NOT NULL
);
In numeric(5, 2), 5 is the precision. It means the total number of digits the value can have. 2 is the scale. It means the number of digits allowed after the decimal point.
So this value fits:
999.99
But this one does not:
1000.00
1000.00 has six digits in total, but numeric(5, 2) only allows five.
The cost is that numeric is heavier than integer and bigint. It can use more storage, and calculations can be slower because the database is working with exact decimal digits instead of simple whole-number types.
So numeric is a strong option when you want exact decimal storage and you do not want to manually convert the amount into the smallest currency unit.
Conclusion
Floating-point types are designed for speed, not precision, and for money, even a tiny rounding error can compound into a real problem over time. The safest approach in most cases is to store money as a whole number in the smallest unit of the currency using integer or bigint. If you do not want to deal with that conversion in your application, numeric gives you exact decimal storage out of the box at the cost of extra storage and slower calculations. Whichever type you choose, the important thing is that the value stored in your database is exactly the value you put in.
Top comments (0)