Decimal and Money Types in Go Databases Done Properly

Money is the place where "close enough" is not. A total off by a cent fails a reconciliation, a rounding rule applied in the wrong place changes an invoice, and floating point makes both easy to do by accident. The solution is well known and still ignored in plenty of code. This article shows the failure with real output, then three correct representations in Go and PostgreSQL, and a rule for splitting amounts, on Go 1.25.6.

Why float64 is wrong for money

Binary floating point cannot represent most decimal fractions exactly. 0.1 is stored as the nearest binary number, which is slightly more or less than a tenth. Operations accumulate the error.

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/shopspring/decimal"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func main() {
	ctx := context.Background()

	// 1. The classic float problem.
	var f float64
	for range 10 {
		f += 0.1
	}
	fmt.Printf("ten times 0.1 as float64: %v (== 1.0: %v)\n", f, f == 1.0)
	var d decimal.Decimal
	for range 10 {
		d = d.Add(decimal.RequireFromString("0.1"))
	}
	fmt.Printf("ten times 0.1 as decimal: %s (== 1: %v)\n", d, d.Equal(decimal.NewFromInt(1)))

	// 2. Splitting a bill: allocate the remainder, do not round each share.
	total := decimal.RequireFromString("100.00")
	third := total.Div(decimal.NewFromInt(3)).Round(2)
	fmt.Printf("100.00 / 3 rounded per share: %s x3 = %s (lost %s)\n", third, third.Mul(decimal.NewFromInt(3)), total.Sub(third.Mul(decimal.NewFromInt(3))))
	shares := total.Mul(decimal.NewFromInt(100)).IntPart() // cents
	base, rem := shares/3, shares%3
	fmt.Printf("in cents: base=%d remainder=%d -> %d, %d, %d\n", base, rem, base+1, base, base)

	// 3. numeric in Postgres keeps exact values and the scale.
	pool, err := pgxpool.New(ctx, dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	_, err = pool.Exec(ctx, `drop table if exists m599; create table m599 (id int, price numeric(12,2), qty int, cents bigint)`)
	if err != nil {
		log.Fatal(err)
	}
	_, err = pool.Exec(ctx, `insert into m599 values (1, $1, 3, 110)`, decimal.RequireFromString("1.10"))
	fmt.Println("insert decimal into numeric(12,2):", err)

	var price decimal.Decimal
	var sumSQL string
	_ = pool.QueryRow(ctx, `select price::text, (price * qty)::text from m599`).Scan(&sumSQL, new(string))
	_ = pool.QueryRow(ctx, `select price from m599`).Scan(&price)
	fmt.Printf("scanned back: %s, times 3 = %s\n", price, price.Mul(decimal.NewFromInt(3)))

	var fsum float64
	_ = pool.QueryRow(ctx, `select (price::float8 * qty) from m599`).Scan(&fsum)
	fmt.Printf("same sum through float8: %v\n", fsum)

	// numeric rounds on the way in; the scale is a constraint, not a suggestion.
	_, _ = pool.Exec(ctx, `insert into m599 values (2, 0.005, 1, 1)`)
	var half string
	_ = pool.QueryRow(ctx, `select price::text from m599 where id = 2`).Scan(&half)
	fmt.Printf("0.005 stored in numeric(12,2): %s\n", half)

	// 4. Integer cents: exact and fast, with the scale in the column name.
	var cents int64
	_ = pool.QueryRow(ctx, `select cents * qty from m599 where id = 1`).Scan(&cents)
	fmt.Printf("integer cents: 110 x 3 = %d -> $%d.%02d\n", cents, cents/100, cents%100)
	_, _ = pool.Exec(ctx, `drop table m599`)
}

go run . 2>&1

ten times 0.1 as float64: 0.9999999999999999 (== 1.0: false)
ten times 0.1 as decimal: 1 (== 1: true)
100.00 / 3 rounded per share: 33.33 x3 = 99.99 (lost 0.01)
in cents: base=3333 remainder=1 -> 3334, 3333, 3333
insert decimal into numeric(12,2): <nil>
scanned back: 1.1, times 3 = 3.3
same sum through float8: 3.3000000000000003
0.005 stored in numeric(12,2): 0.01
integer cents: 110 x 3 = 330 -> $3.30

The first line of the output:

ten times 0.1 as float64: 0.9999999999999999 (== 1.0: false)
ten times 0.1 as decimal: 1 (== 1: true)

Ten tenths do not make one in float64. The error is tiny, 1e-16, and it breaks equality checks, comparisons against limits and anything that formats with exact digits. It also appears in the database: price::float8 * qty gives 3.3000000000000003 for 1.10 times 3, where an exact type gives 3.30. The lab's output line, same sum through float8: 3.3000000000000003, is from PostgreSQL itself.

Three correct representations

1. Integer minor units

Store the amount as an integer count of the smallest unit: cents for dollars and euros, yen for yen. 1.10 is 110. Integer arithmetic is exact and fast, comparisons and sums need no care, and every database and language handles integers identically. In Go it is an int64, which holds amounts up to about 92 quadrillion cents. The lab computes 110 × 3 = 330, displayed as $3.30 by formatting with cents/100 and cents%100.

The costs: you must know the scale for each currency (most have 2 decimal places, some 0, some 3), name the column to say so (price_cents), convert at the edges, and handle fractions of a cent, such as per-unit prices of $0.0004, which do not fit. For one currency with no sub-cent amounts it is the simplest choice and the hardest to get wrong.

2. PostgreSQL numeric with a decimal library

numeric(12,2) stores exact decimals with 12 digits in total and 2 after the point. Declare the scale to match your currency. In Go, use a decimal type. shopspring/decimal is the common choice: decimal.RequireFromString("1.10"), .Add, .Mul, .Div, .Round(2). pgx scans a numeric into a decimal.Decimal and writes one back as a parameter, as the lab shows: the price 1.10 went in, scanned back: 1.1, times 3 = 3.3.

Two behaviours to know. The decimal prints 1.1 and not 1.10: it holds the value, and formatting to two places is d.StringFixed(2). And numeric(12,2) enforces its scale: inserting 0.005 stored 0.01, because the database rounds to two places on the way in. A scale in the column is a rule. If your code sends a value with more precision than the column keeps, the database rounds silently, half away from zero; compute and round in Go with an explicit rule, then store the rounded value.

Other decimal libraries exist: cockroachdb/apd offers arbitrary precision with explicit contexts, and govalues/decimal is fast with fixed bounds. Pick one for the whole code base.

3. A money type that wraps either

A small Money type with an amount and a currency, and methods that refuse to add different currencies, removes a class of bugs: mixing dollars and euros, or forgetting to convert. Libraries such as Rhymond/go-money do this over integer cents; writing a 50-line type of your own is also reasonable. Store the amount and the ISO 4217 currency code as two columns.

Splitting an amount

The lab runs a bill split three ways:

100.00 / 3 rounded per share: 33.33 x3 = 99.99 (lost 0.01)
in cents: base=3333 remainder=1 -> 3334, 3333, 3333

Rounding each share independently loses a cent: three shares of 33.33 add up to 99.99. The correct approach is allocation: divide in the smallest unit with integer division, then hand out the remainder one unit at a time. 10000 cents divided by 3 is 3333 remainder 1, so the first share gets 3334. The sum is exactly 10000. Every allocation function should guarantee that the parts sum to the whole and that no part differs from another by more than one unit. The same rule applies to tax lines, discounts spread over items and payouts. A property test over random amounts and share counts checks it; see property based testing, where the bill splitter is the example.

Rounding rules are business rules

Which way to round halves is a policy, not a technical detail. Banker's rounding (half to even) is the default in some accounting systems, half away from zero in others. decimal.Round rounds half away from zero; RoundBank rounds half to even. PostgreSQL's numeric rounds half away from zero on assignment. When your Go code and your SQL both round, make sure they agree, or round in one place only. Decide where in a calculation rounding happens (per line, or on the total), and write it in a test with the numbers from a real invoice.

Checklist

1. No float32 or float64 in any type that holds money, in Go or in a column.
2. One representation per boundary: integer cents in the database and API, or numeric and a decimal type, and conversions in one place.
3. Currency stored next to the amount.
4. Splitting through an allocation function that conserves the total.
5. Rounding rule documented and tested.
6. JSON: send amounts as strings or as integer cents, because JSON numbers decode to float64 in many clients. A price 19.99 as a JSON number is read as a float; as "19.99" or 1999 it is not.
7. Aggregates in SQL (sum) over numeric are exact; over float they are not.

Testing

Test with awkward numbers: 0.1, 0.2, 0.3, 1.005, 2.675, a third of 100, large totals, negative amounts and zero. Compare decimals with Equal, not ==, since two decimals with different scales are equal in value and not in representation. Round trip through the database in an integration test, as in PostgreSQL tests, reading the values back with struct scanning, because the column's scale affects results. The Go side is described in the shopspring/decimal documentation, and the PostgreSQL manual explains numeric types. For a general explanation of the floating point issue, see the floating-point guide.