Money in PostgreSQL and Java: NUMERIC and BigDecimal

Float and double are binary: 0.1 has no exact representation, the same way 1/3 never terminates in decimal. Summing cents in a double works in the test and gets the customer's statement wrong.

System.out.println(0.1 + 0.2);        // 0.30000000000000004
System.out.println(0.1 + 0.2 == 0.3); // false

One cent of error sounds harmless until it's a sum over a million rows, a reconciliation against the payment provider, or an invoice that fails an audit because the parts don't add up to the total. Monetary bugs are the expensive kind: they're discovered by accountants, not by tests.

In the database: NUMERIC with explicit scale

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    total NUMERIC(12, 2) NOT NULL
);

NUMERIC(12,2) stores the exact value: 12 digits total, 2 after the decimal point. Never REAL or DOUBLE PRECISION for money.

Sizing the precision is a domain question, not a technical one. NUMERIC(12,2) tops out just under 10 billion in currency units, enough for a line item, tight for a company's lifetime revenue column. Cross-currency systems usually standardize on something like NUMERIC(19,4). If the domain has rates with more decimals (FX, daily interest), raise the scale of that specific field, like NUMERIC(18,6), instead of loosening everything: the scale in the DDL is documentation of how precise that quantity really is. A bare NUMERIC with no precision at all is legal and stores anything, which also means it promises nothing; give money columns explicit bounds.

One more trap at the border: aggregating in SQL is exact (SUM(total) over NUMERIC stays NUMERIC), so prefer summing in the database or in BigDecimal, and never let a JSON serializer turn the value into a float on the way out. If the API speaks JSON, money travels as a string or as integer cents.

In Java: BigDecimal, and watch out for equals

@Column(nullable = false, precision = 12, scale = 2)
private BigDecimal total;

Three classic traps:

new BigDecimal(0.1) imports the double's error, the value is already wrong before BigDecimal sees it. Use new BigDecimal("0.1") or BigDecimal.valueOf(0.1).
equals compares scale: new BigDecimal("1.0") is not equal to new BigDecimal("1.00"). That poisons Set membership and assertEquals in tests. To compare value, use compareTo.
Division needs an explicit RoundingMode: total.divide(n, 2, RoundingMode.HALF_EVEN). Without one, a non-terminating result throws ArithmeticException at runtime. HALF_EVEN (banker's rounding) avoids accumulated bias over large sums.

Normalize scale once at the boundary instead of sprinkling rounding everywhere:

BigDecimal amount = input.setScale(2, RoundingMode.HALF_EVEN);

After that, additions and subtractions keep the scale and the invariant "this is money with two decimals" holds through the codebase.

Outside Java

In Python the equivalent is decimal.Decimal, and Pydantic maps NUMERIC to it (never let a route handler float() it on the way in). In TypeScript there's no native decimal type, so either the value travels as a string, or in integer cents, or you accept the risk of number, acceptable for display, not for arithmetic. Whatever the stack, the rule is the same one the DDL states: money is exact decimal, end to end, and every cast to binary floating point is a place the statement can stop adding up.

Pasted a CREATE TABLE with a money column into Schemint? It generates the field as BigDecimal and warns you about rounding.

Money in PostgreSQL and Java: NUMERIC and BigDecimal

In the database: NUMERIC with explicit scale

In Java: BigDecimal, and watch out for equals

Outside Java

Related