Money in PostgreSQL: numeric, integer cents or money?

In money in cents, not floats we explain why a floating-point number cannot hold money. That leaves the practical question every PostgreSQL schema has to answer: which type do you use instead? There are three serious candidates — numeric, a whole number of cents in integer or bigint, and the built-in money type — and the debate about them is usually louder than the difference.

This article puts them side by side, with the behaviour we checked ourselves in psql and with what our own two products actually use. That last part matters: we do not store every amount in integer cents, and we explain why.

Which PostgreSQL type should you use for money?

For most SaaS products numeric with an explicit precision and scale is the right type for money — numeric(14,2) for amounts, and more decimals for unit prices and rates — because it is exact, SQL reports can use it directly, and each column can have the number of decimals it needs. A whole number in the smallest unit (bigint cents) is equally exact and faster, and is the better fit when amounts are only added up and mostly travel between application code and a payment system. Do not use real or double precision, which are inexact, and do not use the money type, which cannot hold fractions of a cent and ties its decimals and formatting to the database’s lc_monetary setting.

The PostgreSQL documentation says the same about numeric: it is “especially recommended for storing monetary amounts and other quantities where exactness is required”.

The types in one table

The column that decides most often is fractions of a cent. The moment you have a unit price of €0.0125, a fuel price per litre or a tax rate of 21.000, integer cents need a second rule for those numbers, and numeric does not.

numeric: exact, and it rounds without telling you

numeric stores decimal digits instead of powers of two, so 0.1 + 0.2 is simply 0.3. Addition, subtraction and multiplication are exact. The cost is speed: according to the PostgreSQL documentation, calculations on numeric “are very slow compared to the integer types”. For a SaaS product that sums a few thousand invoice lines, that is not a limitation you notice; for analytics over hundreds of millions of rows it can be.

Three things we checked on PostgreSQL 16.14 and that you want to know before you rely on it:

• A column with a scale rounds on the way in. 2.345 in a numeric(12,2) column becomes 2.35, with no warning and no error. The documentation confirms it: if the value has more decimals than the column, “the system will round the value”. A tax amount calculated in the application with four decimals therefore lands in the database already rounded — once, at a place nobody chose.
• numeric without precision and scale accepts any number of decimals. The documentation calls that an “unconstrained numeric” column. Then nothing stops an amount of 10.3333333333333333 from being stored. For money, always specify the scale.
• NaN fits. numeric has the special values NaN, Infinity and -Infinity. In our test a numeric(12,2) refused Infinity but accepted 'NaN'. An amount column deserves a CHECK constraint that excludes it.

And numeric does not solve division either. 10.00 / 3 gives 3.3333333333333333; round each of the three shares to cents and add them up, and you get 9.99. The missing cent is not a type problem but an allocation problem, and the solution — divide down, then hand out the remaining cents to the largest remainders — is in the article on cents.

Integer cents: fast, and division throws away the remainder

A whole number of cents is exact and as fast as a number can be, and as long as it stays below the limits below it fits in an ordinary JavaScript number. Three limits come with it:

• integer stops at €21,474,836.47. The maximum of a four-byte integer is 2,147,483,647, and in cents that is just over 21 million euros. A monthly subscription never reaches that; a yearly total across all customers can. Exceeding it raises an integer out of range error, so it does not go wrong silently — it goes wrong in production. For totals, use bigint.
• bigint does not fit in a JavaScript number. Number.MAX_SAFE_INTEGER is 9,007,199,254,740,991; above that, a JavaScript number is no longer exact. That is why node-postgres returns bigint as a string by default. In cents the limit is about 90 trillion euros, so the amount itself is safe — but code that does Number(row.total) everywhere without thinking has lost the guarantee.
• Division in SQL truncates. 1000 / 3 gives 333, and -1000 / 3 gives -333: the remainder simply disappears, according to the documentation “towards zero”. A report that splits amounts in SQL can lose a cent on every line without an error.

And a unit price with four decimals does not fit in cents. You then need a second column with a different unit, or a rule that says which columns count in hundredths of a cent — and that is exactly the kind of second shape of an amount that ends up being added to the first by somebody.

The money type: why the PostgreSQL wiki advises against it

money looks like the obvious choice, and it is the one type of the three the PostgreSQL wiki explicitly tells you not to use. Its fractional precision “is determined by the database’s lc_monetary setting”, the output is formatted for that locale, and according to the documentation loading a dump into a database with a different lc_monetary “might not work”. Dividing money by an integer truncates, and dividing money by money yields a double precision — exactly the type you were trying to avoid.

The PostgreSQL wiki page Don’t Do This is blunt about it: money “doesn’t handle fractions of a cent”, and its rounding “is probably not what you want”. It names one exception: a single currency, no fractions of a cent, and only addition and subtraction. Few SaaS products stay inside that box for long.

What we use in our own products

Both of our products run on PostgreSQL, and neither stores all money in cents.

booxx, our Dutch bookkeeping product, stores the general ledger in numeric(14,2): debit and credit per journal line. Invoice lines have a unit_price in numeric(12,4) and a quantity in numeric(12,3), and VAT rates are numeric(6,3). A bookkeeping product needs different scales side by side, and the ledger speaks the same language as the reports and the tax filing: euros with two decimals.

The billing layer of booxx — what an accounting firm receives or pays per month, and what a subscription costs per administration — does use whole cents in an integer. The migration says why in one line: amounts in cents (integer) — this is the billing layer, not the bookkeeping. Those are fixed prices that are only added up, and there the simpler type wins.

Pilot-Next, our product for flying clubs, stores invoice totals, VAT amounts and the balance after every transaction in numeric(12,2) through Prisma. In the application those values arrive as Decimal.js objects rather than JavaScript numbers, so the exactness survives the step from database to code.

The rule behind both choices: the type follows what the amount has to do. Amounts that get multiplied, split and reported with different precisions go in numeric with an explicit scale. Amounts that are only counted and passed on go in whole cents. And in both cases there is one type in the code that knows it is money.

Check your own database in one query

This query lists the columns that deserve a second look: floating-point and money columns, numeric columns without a scale, and four-byte integers whose name suggests an amount.

SELECT table_name, column_name, data_type, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND (
        data_type IN ('real', 'double precision', 'money')
     OR (data_type = 'numeric' AND numeric_scale IS NULL)
     OR (data_type = 'integer' AND column_name ~ '(amount|cents|price|total|balance)')
  )
ORDER BY table_name, column_name;

What to do with the result:

• real or double precision holding an amount is the only real emergency. Every row may already contain a deviation, and converting it later means deciding which value was right.
• money works as long as you stay within one currency and do not divide. Plan the move to numeric before that stops being true.
• numeric without a scale is exact, but does not stop an unrounded value from being stored. Give the column a scale once you know which one.
• integer in cents is fine for prices and subscriptions. For totals and balances that grow, check whether 21 million euros is truly out of reach.

Adjust the column names in the last condition to your own naming.

Can you change the type later?

Technically it is one ALTER TABLE … ALTER COLUMN … TYPE statement. In practice the difficulty is not the statement but the data: converting a double precision column to numeric(14,2) rounds values that were already slightly off, and for every invoice that has gone out the question is which number was right — the stored total or the sum of the lines. That is why money is one of the three decisions in a SaaS product you never get to undo.

If you would rather have somebody else make these choices from the first migration: that is precisely the work we do when we build a SaaS product.