Integer vs Decimal Money Postgres: Two ERPs, Two Answers

I have two Postgres-backed products on my desk that disagree about what a rupiah is. A carwash point of sale stores every money field as a plain Int. A client ERP I built for accounts receivable and payable stores the same kind of field as Decimal with precision 15 and scale 2. Both are in production, both are correct, and the difference is not taste.

This post is the decision, not the case against floats, which I covered in a separate article. It compares the two designs on rounding, percentage maths, serialization and multi-currency, using the schemas and helpers from both codebases rewritten as generic examples. Where a claim is a design choice rather than a measurement, I say so. I did not benchmark either design.

Two products, two storage decisions

The carwash POS keeps order line prices, payments, advances and payroll figures as Int. The rupiah has a subunit on paper, but nobody pays in it, so the minor unit and the major unit are the same unit and no scaling is needed. The ERP is different in two ways: it carries a currency code on its invoices, and its money columns use Decimal with precision 15 and scale 2, which leaves room for fractional amounts.

The case for integers over floats is already written up in why we store money as integer rupiah. The question here is narrower and more useful: once you have ruled out float, is the exact type an integer count of minor units, or a fixed-point NUMERIC? The answer depends on four things you can check before writing a migration.

• Does the currency have a subunit anyone really uses?
• How large can one row and one total get?
• How many currencies share the column?
• Who consumes the number: your own code, or other people's clients?

What each choice is in Postgres and Prisma

The two schemas below are generic, but they have the same shape as the real ones. The integer version stores a count. The Decimal version stores a fixed-point number with a declared scale, and carries the rate that produced the tax so a later change to master data cannot rewrite history.

// Product A: integer rupiah. Minor unit == major unit, so no scaling.
model OrderLine {
  id        String @id @default(uuid())
  unitPrice Int    // postgres integer: 4 bytes, max 2147483647
  quantity  Int
  lineTotal Int    // computed and rounded in code BEFORE it is stored
}

// Product B: fixed-point NUMERIC with a currency code beside it.
model InvoiceLine {
  id          String  @id @default(uuid())
  subtotal    Decimal @db.Decimal(15, 2) // 13 integer digits + 2 decimals
  taxRate     Decimal @db.Decimal(5, 2)  // snapshot of the rate USED
  taxAmount   Decimal @db.Decimal(15, 2)
  totalAmount Decimal @db.Decimal(15, 2)
  currency    String  @default("IDR")
}

// Without @db.Decimal(p, s), Prisma maps Decimal to decimal(65,30).

The numbers matter. PostgreSQL documents integer as 4 bytes with a range of minus 2147483648 to plus 2147483647, and bigint as 8 bytes. NUMERIC with precision 15 and scale 2 holds 13 digits before the decimal point and 2 after. Prisma maps Int to integer and returns a plain number, maps Decimal to a decimal.js object, and maps BigInt to a JavaScript BigInt. If you do not give Decimal a native type, Prisma defaults it to decimal(65,30), which is almost never what you want for money.

Rounding and percentage maths

Rounding is where the two designs genuinely differ. With integers, the database never rounds anything: by the time a value reaches the column, your code has already decided what the whole-unit amount is. With NUMERIC, the database can round, and PostgreSQL documents that numeric rounds ties away from zero while double precision rounds ties to the nearest even number on most machines. That difference alone is a reason never to let a float anywhere near a rounding step.

Percentage maths is the same problem in both designs: multiply a base by a rate, then round. I keep three rules from both codebases. Store the rate on the row that used it, so changing a master rate never rewrites a posted document. Compute and round each component separately, then add, instead of adding unrounded parts and rounding once at the end. And have exactly one rounding function that every path calls, so an accounts payable screen and an accounts receivable screen cannot disagree by a unit.

// Wrong: a float pipeline feeding a Decimal column.
const tax = Number(line.subtotal) * Number(line.taxRate) / 100;
const stored = Math.round(tax); // a true .5 can arrive as 5.4999999999999996

// Right (integers): one primitive, multiply first, round once per component.
export function roundUnit(x: number): number {
  const eps = Math.abs(x) * Number.EPSILON + 1e-9; // guard for float artefacts
  return Math.floor(x + 0.5 + eps);                // half up
}
export const percentOf = (base: number, rate: number) =>
  roundUnit((base * rate) / 100);

// Right (Decimal end to end): never touch Number.
import { Prisma } from "@prisma/client";
const taxD = new Prisma.Decimal(line.subtotal)
  .mul(line.taxRate)
  .div(100)
  .toDecimalPlaces(0, Prisma.Decimal.ROUND_HALF_UP); // rm 4 = ROUND_HALF_UP

If a stored total is purely derived from other columns, you can also let the database own it; generated columns for computed data covers when that is a good idea.

Serialization: where Decimal costs you

An Int comes out of Prisma as a number and goes into JSON untouched. A Decimal comes out as a decimal.js object. Its toJSON returns a string, so a naive response ships money as a string, and every client has to know that. decimal.js also does not keep trailing zeros, so a value stored with two decimals can stringify without them unless you call toFixed. Either decide on strings with a fixed scale as your API contract, or convert explicitly at the boundary in one place.

// Int: a number, serialises untouched.
res.json({ total: order.lineTotal });

// Decimal: toJSON() yields a string, and decimal.js drops trailing zeros.
res.json({ total: invoice.totalAmount.toFixed(2) }); // always a string

// BigInt (e.g. SUM over an integer column via a raw query):
JSON.stringify({ revenue: 10n });  // TypeError: BigInt cannot be serialised
JSON.stringify({ revenue: 10n }, (_k, v) =>
  typeof v === "bigint" ? v.toString() : v,
);                                  // replacer converts at the boundary

BigInt has its own trap. MDN documents that JSON.stringify on any BigInt value throws a TypeError, and the Prisma docs show the same failure with a replacer function as the workaround. This matters even in an integer design, because PostgreSQL's sum over an integer column returns bigint, so a report built on a raw query hands you a BigInt even though every stored column is an Int. I convert those to strings at the API boundary rather than storing BigInt in every row.

Decimals also cost you in plain comparisons. My audit diff helper has to normalise a Decimal and a number to the same form before it can say whether a field changed, or an unchanged amount shows up as an edit. Integers compare with a triple equals sign and need none of that.

Multi-currency changes the answer

Integer minor units work cleanly for rupiah because the minor unit is the major unit. The moment a second currency shares the column, a bare integer is ambiguous: is the stored value a count of whole units or of hundredths? You need a per-currency exponent, stored or looked up, and every display and conversion path must apply it. The ERP sidesteps that by keeping a currency code beside a fixed scale-2 amount.

That is not free either: a scale of two assumes every currency you accept has two decimal places. For the accounting side of conversion, rate tables and gain or loss entries, see ERP multi-currency accounting. My rule is that a single-currency system with a coarse unit earns integers, and anything that may become multi-currency should start on NUMERIC.

The decision table

This is the comparison I would hand a teammate. It is a design comparison, not a benchmark: PostgreSQL's own documentation says numeric calculations are slow compared with integers, but I have not measured that gap on either product and would not claim a size for it.

If your column is both multi-currency and large, the table is not a close call. If it is single-currency and small, the integer wins on simplicity, and the savings show up in every query, serializer and test you do not have to write.

The failure I would warn you about: Decimal storage, float arithmetic

Reading the ERP's schema and helpers side by side taught me something uncomfortable. The columns are Decimal, the DTO bounds the maximum to what Decimal(15,2) can hold, and yet the tax helper does its arithmetic on ordinary JavaScript numbers and rounds to a whole unit. A Decimal column guarantees what you store, not how you got there.

The helper carries a small epsilon guard, and its own comment records why: a result that is mathematically exactly one half materialised as 5.4999999999999996, and a plain round would have truncated it. The guard works, and I would still rather not need it. This is a design observation from reading the code, not an incident report; I am not claiming it ever produced a wrong invoice.

What I would do differently, and a Monday checklist

Limits first. Both designs are judgement calls from my own projects, not a survey of the field. I have no performance numbers, I have not run either product with a second currency on the integer side, and the epsilon guard above is the kind of thing I would replace with Decimal arithmetic or integer maths if I were starting the ERP over.

On Monday, in the order I would do it:

1. Write down the currency, the largest single value and the largest aggregate you expect, then compare them with the range of the type.
2. Pick one rounding function and make it the only way a stored amount is produced; delete every other call to a rounding primitive.
3. Store the rate beside every computed percentage so a master data change cannot rewrite a posted row.
4. Decide your JSON contract for money, number or string, and write a test that fails if a bigint or Decimal object reaches the response.
5. Add a validation bound to the DTO equal to the column's limit, so overflow is a 400 rather than a 500.

Pick the representation from the currency and the consumers, then enforce it end to end. Integers win when the unit is coarse, the range is bounded and you own the clients. NUMERIC wins when fractions, several currencies or other people's integrations are in play. Whichever you choose, one rounding function and no floats in the pipeline matter more than the type.