SQLite Integer Cents: Store Decimal Amounts and Verify Exact Totals
For this SQLite example, a payment of $0.29 belongs in the database as 29 integer cents. Keep that integer through the calculation, then format it as dollars for display. The important distinction is between storing an exact amount and making an approximate amount look tidy.
This walkthrough accompanies the Leverage Lab Studios video below. It uses four fictional, nonnegative payments and a two-decimal currency scale. It is a small, reproducible software exercise, not a complete accounting system.
What you will verify
- The starting payments, 10, 20 and 10001 cents, total 10031 cents, displayed as 100.31.
- The decimal text
12.345is rejected before a database write because it contains a fractional cent. - The text
0.29becomes exactly 29 cents. - The four final rows total 10060 cents, displayed as 100.60, matching an independent decimal-text calculation.
1. See why rounding the display is insufficient
Run this query in a disposable SQLite database:
SELECT
0.1 + 0.2 = 0.3 AS exact_equality,
printf('%!.17g', 0.1 + 0.2) AS binary_sum;The equality result is 0. Binary floating point cannot represent every decimal fraction exactly. That is a property of the representation, rather than a SQLite-specific arithmetic defect. SQLite’s floating-point documentation explains the difference between exact integers and approximate REAL values.
Another trap is converting an existing binary float by multiplication and truncation. In the filmed Python example, int(float('0.29') * 100) produces 28. Formatting that value with two decimal places cannot recover the missing cent. Parse the original text before introducing a float.
2. Store the smallest unit as an integer
Use SQLite 3.37.0 or later for this STRICT-table example. Work in a new practice database; do not paste schema changes into your only production copy. Our beginner’s backup guide explains why a recoverable independent copy matters. A live SQLite database also needs a SQLite-aware consistent backup procedure, such as the online backup API.
CREATE TABLE payments (
id TEXT PRIMARY KEY,
amount_cents INTEGER NOT NULL CHECK(amount_cents >= 0)
) STRICT;
INSERT INTO payments VALUES
('P1', 10),
('P2', 20),
('P3', 10001);
SELECT id, amount_cents, typeof(amount_cents) AS storage
FROM payments
ORDER BY id;All three amounts have SQLite storage type integer. The table separately requires a value and rejects negative amounts. STRICT tables can perform lossless type conversions; they do not replace validation of your input format or currency rules.
3. Sum cents, then format the result
SELECT
SUM(amount_cents) AS total_cents,
printf('%d.%02d',
SUM(amount_cents) / 100,
SUM(amount_cents) % 100) AS display_dollars
FROM payments;The starting result is 10031 | 100.31. The first column remains an integer total. The formatted second column is text for people to read. This formatting expression is deliberately limited to the nonnegative, nonempty dataset shown here.
4. Validate decimal text before inserting it
The following is the input-validation approach used by the filmed importer. It accepts ordinary nonnegative decimal text with one to seventeen whole-number digits and at most two decimal places. It rejects scientific notation, signs, commas, non-finite values, fractional cents and values outside SQLite’s signed integer range.
import re
from decimal import Decimal
def parse_cents(text):
if not re.fullmatch(r'[0-9]{1,17}(?:[.][0-9]{1,2})?', text):
raise ValueError('use 1-17 whole digits and at most 2 decimal places')
amount = Decimal(text)
cents = amount * 100
if (not amount.is_finite() or amount < 0
or cents != cents.to_integral_value()
or cents > 9223372036854775807):
raise ValueError('whole cents must be nonnegative and in range')
return int(cents)
assert parse_cents('0.29') == 29Call the parser before opening the write operation. Insert the resulting integer with bound parameters, for example connection.execute('INSERT INTO payments VALUES (?, ?)', ('P4', parse_cents('0.29'))), then commit. For 12.345, the parser raises an error before insertion. A rejected attempt must leave the row count and total unchanged.
5. Reconcile against an independent source
After adding P4, repeat the totals query. Expect 10060 | 100.60. Independently sum the original text amounts 0.10, 0.20, 100.01 and 0.29 with Python’s Decimal, and compare that result with the stored integer total divided by 100 using decimal arithmetic. This checks the source-to-database bridge, rather than comparing two differently formatted copies of the same SQL result.
SELECT COUNT(*) FROM payments; -- 4
PRAGMA integrity_check; -- okAn integrity check is useful, but it does not establish that the business total is correct. Keep both the source reconciliation and database checks.
Before adapting the pattern
Decide the currency and smallest permitted unit explicitly. This exercise does not mix currencies, accept refunds, calculate tax or choose a rounding policy. Individual rows and their accumulated total must remain within the supported integer range. Empty datasets and negative display formatting need their own handling. Start with the boundary cases your application actually permits.
Browse more video tutorials and written walkthroughs. For protecting the resulting database and source files, continue with the 3-2-1 backup strategy guide.
James Kennedy is a writer and product researcher at Drives Hero with a background in IT administration and consulting. He has hands-on experience with storage, networking, and system performance, and regularly improves and optimizes his home networking setup.




