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”. How slow is measured further down: 1.3 to 3 times slower than bigint on whole queries over 10 million rows, with one exception of 9 times that is a property of the sort and not of the arithmetic. A product that sums the invoice lines of one tenant does not notice.

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 happens to a money column when lc_monetary changes?

The stored value does not change, but what it means does: a money column holds a whole number of the smallest unit, and lc_monetary decides, on every read and every write, how many of its digits are decimals. We stored 12.34 in a money column on PostgreSQL 17.11 with lc_monetary at en_US.utf8, then changed only that setting in the session and read the same row again (13 October 2026):

None of the reads and inserts in this table raised an error or a warning. lc_monetary is an ordinary setting (context = user in pg_settings) that a role can change for its own session, to any locale installed on the server; a role with only SELECT and INSERT on the table did so in our test.

What goes wrong depends on the locale. Under the locale without decimals, every amount written with two decimals read a factor of a hundred too large, and every input went wrong: the text '12.34', the value 12.34::numeric and the integer 12 all ended up as what en_US.utf8 reads as $0.12. Under the decimal-comma locale the stored amounts read correctly and a numeric value was stored correctly; only text input went wrong, because the point is dropped: '12.34' was stored a hundred times too large, '1.5' ten times. The reverse happens too: with en_US.utf8, the literal '12,34' became $1,234.00.

A dump is where it gets loud. pg_dump writes the column as text formatted with the lc_monetary of the dump session ($12.34 and $1,000.00 under en_US.utf8; the same rows came out as ￥1,234 and ￥100,000 when we dumped under ja_JP.UTF-8) and records no lc_monetary in the dump. Loading the en_US.utf8 dump with lc_monetary at nl_NL.UTF-8 failed with invalid input syntax for type money: "$12.34". That is the case the documentation warns about, and it is the better of the two failures, because there is an error to see. It did not stop the restore, though: psql carried on, exited with 0 and left the table empty. Run a restore with ON_ERROR_STOP.

The type also has three rounding rules. Division by an integer truncates: '10.00'::money / 3 gave $3.33 and '0.15'::money / 2 gave $0.07. Multiplying or dividing by a fractional number rounds a half to the even cent: '0.05'::money * 0.5 gave $0.02, '0.15'::money * 0.5 gave $0.08 and '0.15'::money / 2.0 gave $0.08. Input rounds a half away from zero: '0.025'::money gave $0.03. And '10.00'::money / '3.00'::money gave the double precision value 3.3333333333333335.

How much slower is numeric than bigint?

In our measurement a whole query over 10 million rows took 1.3 to 2 times as long on numeric(14,2) as on bigint cents, and 3 times as long when every row is multiplied and rounded. One query fell outside that range: a sort with a large LIMIT or OFFSET was 9 times slower, for a reason explained below. Stored, the two types were the same size in one table layout and 16 per cent apart in another, and an index over repeated amounts was four times larger on numeric. Measured on 13 October 2026 on PostgreSQL 17.11.

The setup: five tables with the same 10 million amounts, drawn uniformly between 0.02 and 99,999.98, so nine in ten have five digits before the point. Each table has a bigint id, an integer tenant with 500 values, and one amount column. One process, no parallel workers, JIT off, work_mem at 2 GB so that every sort and hash stays in memory, tables frozen and cached (shared_buffers 1 GB, the rest in the operating system’s cache). Times are the execution time from EXPLAIN (ANALYZE, TIMING OFF): the rows are produced but not sent to a client. Each is the median of seven runs (five for the full sort and the top ten). The index was built once per type, with maintenance_work_mem at 1 GB and no parallel workers. The machine is a desktop with an eight-core Ryzen 7 3700X.

These are ratios of whole queries. Every one of them also pays about 425 ms just to walk the 10 million rows (a bare count(*), the same on the numeric and the bigint table). Subtract that and the addition itself is 2.9 times slower on numeric; the more a query does that has nothing to do with the amount, the smaller the difference becomes. With PostgreSQL’s default settings (two parallel workers next to the leader, JIT on) the same sum took 354 ms against 237 ms: about the same ratio, fewer milliseconds for both. money sorts slower than bigint although it is the same eight bytes; we did not look into why.

Apart from that sort, the VAT row differs most, and it is the work a ledger does all day. In numeric it is round(amount * 0.21, 2). In cents it is (amount * 21 + 50) / 100. Both returned the same total over the 10 million rows, 105,015,588,888.10. The integer version rounds half up for positive amounts only: for an amount of −0.50 it gives −0.10 where round gives −0.11, so a credit note needs its own branch.

In double precision there is no round with a number of decimals: round(double precision, integer) does not exist, so the value has to be cast to numeric first, and that cast is what makes the inexact type the slowest in that row. The version without the cast, round(amount * 100 * 0.21) / 100, returned a total of 105,015,588,386.86, which is €501.24 too low: round on a double precision rounds a half to the nearest even number (round(2.5) gives 2 there and 3 on numeric).

For a product that sums the lines of one tenant, the difference in arithmetic is not noticeable: summing the 20,000 rows of one tenant through the index took 3.8 ms on numeric and 3.0 ms on bigint. It starts to count in a report over every tenant at once, when building an index on a large table, and in the sort below.

Why was the sort with OFFSET nine times slower?

Fetching the last row with ORDER BY amount OFFSET 9999999 LIMIT 1 took 17.8 s on the numeric column, while sorting all rows without a limit took 4.0 s. PostgreSQL sorts numeric quickly by first comparing a compact abbreviation of each value, eight bytes on a 64-bit build, and it switches that off for a sort with a bound: the source says “Bounded sorts are not an effective target for abbreviated key optimization” (tuplesort.c, PostgreSQL 17). A bigint is compared directly and has no abbreviation to lose, which is why its time does not change (2.0 s both ways).

A top ten hardly feels it (1.9 times in the table, like the full sort). The cost grows with the bound: on a table of one million rows, sorting everything took 305 ms on numeric, LIMIT 100000 took 617 ms and LIMIT 1000000 took 1,302 ms, while on bigint the limit of 100,000 was faster than the full sort (149 ms against 184 ms). Deep pagination and large limits over an amount column pay it.

Does numeric take more disk space than bigint?

A numeric amount takes 3 bytes for zero and 11 for a twelve-digit amount with cents, against a fixed 8 for bigint and 4 for integer. numeric stores a three-byte header plus two bytes for every group of four decimal digits, counted from the decimal point, and drops groups that are all zero at the end. So the size follows the digits, not the magnitude:

The 10 million amounts in our tables took 5 to 9 bytes, 8.8 on average: 89 per cent of them are 10,000.00 or more with cents, and those take nine. The tables did not follow that number:

MB is what pg_size_pretty reports, 1,048,576 bytes. In the first layout the numeric and the bigint table are the same size to the byte, and in the second integer saves nothing over bigint. PostgreSQL pads every row to a multiple of eight bytes and aligns a bigint on eight, while a short numeric needs no alignment. After a four-byte tenant the bigint wastes four bytes and the numeric uses them; after an eight-byte id a nine-byte numeric spills one byte into the next eight. The type of a column and its position decide together.

The last row is the one that can matter. A B-tree stores a repeated key once with a list of row pointers, and according to the PostgreSQL documentation “numeric cannot use deduplication”, because equal values may differ in display scale. Our random amounts hardly repeat within a tenant, so the first index is the same size for both types. With 200 distinct prices over 2 million rows the same index was 60 MB on numeric and 14 MB on bigint.

What a sum over double precision returns

The same 10 million amounts add up to exactly 500,074,230,467.84. The numeric, bigint, integer and money columns returned that. The double precision column returned a different total every time the plan or the row order changed, and with a parallel plan on every run:

Seven cents too many in table order, about five too few in reverse order, and a different fraction of a cent on every parallel run. A floating-point sum depends on the order of the additions, and a parallel plan does not fix that order: the workers take blocks as they come. A total that changes between two runs of the same query is the practical form of the argument in money in cents, not floats.

sum over a bigint column returns numeric

sum(integer) returns bigint, but sum(bigint) returns numeric, which has room for any total. The total therefore arrives in the application as a numeric and not as the column’s type: code that expects whole cents in the type of the column gets a decimal instead. The cost in the database is small, 608 ms against 570 ms for sum(integer) in the table above.

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.

The column type is one of six things a multi-tenant product locks into its database; the other five, and which of them you can still change later, are in multi-tenant SaaS on PostgreSQL: six decisions.

The other column on an invoice that PostgreSQL does not get right by default is its number: how to give every tenant its own series without duplicates or gaps, measured under load, is in invoice numbers per tenant in PostgreSQL, without gaps.