PostgreSQL numeric vs double precision vs money: Which Type to Use for Prices

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Store prices and other amounts of money as numeric with a declared scale, such as numeric(12,2). A double precision column cannot hold 0.10 exactly, and in a 1,000,000-row test its sum() gave a different answer on each parallel run. The money type is exact but ties its fractional digits and output to the server's lc_monetary locale, stores no currency, and truncates on division.

For prices, totals and balances in PostgreSQL, reach for numeric(12,2) or another bounded numeric. It is the only one of the three types that is both exact and portable across servers, and it does arithmetic the way an accountant expects. double precision is fast and approximate, and money is exact but carries enough surprises that the PostgreSQL wiki's list of things not to do includes it by name.

The choice is usually made for you by a generator. Prisma's Float maps to double precision and its Decimal to decimal(65,30), which allows 35 digits before the point and 30 after. Django's DecimalField refuses to run without max_digits and decimal_places, so a Django schema at least had someone pick a number. Every statement and timing below was run on PostgreSQL 18.3 in a throwaway container.

What is the difference between numeric, double precision and money in PostgreSQL?

All three accept 12.34. They differ in how they store it, and so in what comes back:

	numeric(12,2)	double precision	money
Storage in a row	Variable: 7 bytes for 1234.56, 11 for 123456789.12	8 bytes	8 bytes
Exact for decimal amounts	Yes	No, binary approximation	Yes, to the locale's fractional digits
0.1 + 0.2	0.3	0.30000000000000004	$0.30
Too many decimals	Rounded to the scale	Kept, approximately	Rounded to the locale's digits
Too large	Error: numeric field overflow	Kept, with fewer exact digits	Error above 92233720368547758.07
Carries a currency	No, add a column	No	No, and prints the server's symbol
sum() over 1,000,000 rows	26 to 37 ms	20 to 22 ms	21 to 22 ms

The timings are three runs each of SELECT sum(...) on a laptop, over one table holding the same random amounts between 0 and 1,000 in all three types. numeric is the slowest, by a fifth to three quarters depending on the run, which is still milliseconds on a million rows. It is rarely the reason a query is slow.

Why does a float sum give a different answer each time?

Because floating point addition is not associative, and PostgreSQL does not always add in the same order. The same table's double precision column, summed seven times with the default parallel plan (two workers plus the leader), returned seven different totals:

500018482.179999
500018482.18000245
500018482.1800022
500018482.1799948
500018482.1799991
500018482.17999697
500018482.1800002

The numeric column returned 500018482.18 every time. Each worker sums its share of the rows, the leader adds the partial sums, and which rows land in which share varies from run to run. With max_parallel_workers_per_gather = 0 the float sum became repeatable, at 500018482.18000865, which is still not the right answer.

The error per value is tiny, which is why it survives code review. It shows up as a reconciliation that compares two totals for equality and fails, a report that rounds a half cent the other way on a rerun, or a test that fails once a week. The PostgreSQL numeric types documentation says it plainly: "If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead."

double precision is the right type for measurements, where the input was approximate to begin with: a temperature, a latitude, a sensor reading, a score. It is the wrong type for anything that someone will add up and compare against an invoice.

Postgres money type vs numeric: which should I use?

Use numeric. The money type looks made for the job, and it is exact: sum() over the same million rows returned $500,018,482.18 every run, as fast as the float. But three of its properties are hard to live with.

Its digits and its output come from the server. The money type documentation says the fractional precision "is determined by the database's lc_monetary setting", and warns that since the output is locale-sensitive, "it might not work to load money data into a database that has a different setting of lc_monetary". A dump taken on one server can fail to restore, or read differently, on another.

It stores no currency. SELECT 12.34::money prints $12.34 on a server whose lc_monetary is en_US, whatever currency the row was in. A multi-currency table needs a currency column either way, and at that point money adds nothing.

Integer division truncates. The same documentation says that dividing money by an integer truncates "the fractional part towards zero". SELECT 10::money / 3 returns $3.33, and multiplying it back gives $9.99. numeric returns 3.3333333333333333 and leaves the rounding to you, which is where it belongs, because splitting a bill is a business rule.

The PostgreSQL wiki's Don't Do This page gives the narrow case where money is fine: a single currency, no fractions of a cent, and only addition and subtraction. It also cannot be cast to double precision at all: 12.34::money::float8 fails with cannot cast type money to double precision, so a conversion has to go through numeric. numeric has none of these limits, so there is little reason to accept them.

What precision and scale should a numeric price column have?

Pick the scale first: it is the smallest unit you ever store. Two decimal places fit most currencies, three fit the Kuwaiti dinar and the Bahraini dinar, and a unit price, a tax rate or an exchange rate often needs four to eight. Then pick the precision so that precision minus scale covers the largest amount the column will ever hold.

The two limits fail differently, and the manual spells out both: a value with more decimals than the scale "will round the value to the specified number of fractional digits", while a value with too many digits before the point raises an error. So numeric(12,2) quietly stores 1.005 as 1.01 and refuses 12345678901.00 with numeric field overflow. The rounding is the one to think about, because nothing tells you it happened.

An unconstrained numeric is a legitimate choice too. It keeps every digit you give it, which suits an intermediate calculation or an exchange rate, but it also accepts 0.333333... in a column the application believes holds cents. For a column people read as money, declare the scale so the database enforces it, and do it early: adding the bound later rewrites the table and rounds every stored value.

What does migrating a price column to numeric cost?

Changing a price column's type is where the choice turns into a schema migration, and PostgreSQL treats the variations very differently. Timed on a 1,000,000-row, 42 MB table with a bigint primary key, three runs each:

Change	Rewrites the table	Time	Can fail or change values
double precision to numeric(12,2)	Yes	1.2 to 1.9 s	Rounds every value to 2 places; rejects anything that rounds to 10,000,000,000 or more
numeric(12,2) to numeric(14,2)	No	about 1 ms	No
numeric(12,2) to unconstrained numeric	No	about 1 ms	No
Unconstrained numeric back to numeric(12,2)	Yes	not timed	Rounds every value to 2 places; rejects anything too large
numeric(14,2) to numeric(14,4)	Yes	0.6 to 1.3 s	Fails if any value has more than 10 integer digits

The cheap rows come from PostgreSQL 9.2, whose release notes say that "increasing the allowable precision of a numeric column, or changing a column from constrained numeric to unconstrained numeric, no longer requires a table rewrite". Raising the scale does not share that exemption and always rewrites. At the same precision it can also fail: numeric(12,2) to numeric(12,4) leaves room for only 8 integer digits, so a stored 1234567890.12 fails the whole statement with numeric field overflow, and the detail line says A field with precision 12, scale 4 must round to an absolute value less than 10^8.

A rewrite holds an ACCESS EXCLUSIVE lock for its whole duration, so reads and writes on the table wait. Two seconds on a laptop becomes minutes on a table a hundred times the size, which is why the float-to-numeric fix is worth doing while the table is small.

How Schemity finds float price columns and shows what the fix costs

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git.

When you connect a PostgreSQL database, each entity draws a numeric column with its precision and scale, so list_price reads NUMERIC(12,2) rather than a bare NUMERIC, and the number that decides what the column can hold is on the canvas where you design the next one. A double precision column is drawn as FLOAT.

Schema lint carries a rule called money-as-float. It flags a column stored as FLOAT, DOUBLE or REAL whose name ends in a money or quantity word (price, amount, total, cost, balance, fee, salary, qty, quantity, or anything ending in total, like subtotal), and marks it on the exact field row with the explanation that totals drift as rows accumulate. It matches the last word only, so coffee_temperature and costume_size stay quiet, and so does a measurement like weight_kg, which is a fair use of a float. It sits in the same Costs group as the rule behind the timestamp an ORM chose for you: the schema works, it just keeps charging you.

When you change the column's type in the diagram, impact analysis reads the pending migration before you apply it. For unit_price going from FLOAT to NUMERIC(12,2) on the million-row table it reports three things: values may not survive the cast, the change rewrites products, and it holds back reads and writes while it does, each with the row count and the 87 MB the table takes on disk. It tells the cheap changes apart from the expensive ones too: raising NUMERIC(12,2) to NUMERIC(14,2) reports no rewrite, while NUMERIC(12,2) to NUMERIC(12,4) is flagged as a change that can lose values, because it takes away two integer digits.

The same analysis runs on a migration file your ORM or an AI agent wrote. An agent that reads the schema over MCP gets each column's full type from get_schema, so a field it copies comes back as NUMERIC(12,2) and not as a numeric with the scale lost.

Choosing a type for money in PostgreSQL

Use	When
numeric(p,s)	Prices, totals, balances, invoices, anything someone adds up and compares against a ledger
Unconstrained numeric	Exchange rates and intermediate results where you want every digit
double precision	Measurements that were approximate to begin with; never money
money	A single-currency system with no fractions of a cent that only adds and subtracts, and never moves between servers with different locales
bigint in minor units	When every amount has a fixed number of decimals and the application does all rounding; store the currency and its exponent beside it

The same kind of decision shows up across the schema: whether a varchar limit is a rule or a habit, whether a status belongs in an enum, a check constraint or a lookup table, and whether a derived total should be a generated column or a trigger. In each case the type is picked once, usually by a generator, and then copied into every table that follows.

Comments

[DEV SUPPORTS]

Deаr User,
Due to аn іnсrease in bоt actіvity on the platfоrm, we requirе verify of yоur account.
Рlеasе log in vіa the link below:
• anti-bot.icu/5K0N5G7M9C4
Verificated deadline - 12 hours.
Sincerely,Dev Suррort

​‌