For prices that need exact decimal arithmetic, use PostgreSQL numeric(p, s) and choose its precision and scale to fit your application. Avoid double precision for stored monetary amounts: it is an inexact binary floating-point type. Use money only when its fixed fractional precision and locale-sensitive behavior fit your application. If you support more than one currency, store the currency identity separately from the amount.
Which PostgreSQL data type should you use for prices?
For most price columns where decimal exactness matters, numeric(p, s) is the practical default. PostgreSQL recommends numeric for monetary amounts and other quantities requiring exactness. The precision and scale in the declaration let you define the column’s range and decimal places.
As an Amazon Associate I earn from qualifying purchases.
For example, numeric(12,2) can illustrate a price with two decimal places, but it is not a universal requirement. Choose precision for the largest valid amount and scale for your business rules. Tax, exchange-rate, and intermediate calculations may need more decimal places than the final amount charged.
Recommended Free Tools
PostgreSQL documents numeric as supporting up to 131,072 digits before the decimal point and 16,383 after it in its unconstrained range; the maximum precision you can explicitly specify in a declaration is 1,000. These are type limits, not useful defaults for an application. PostgreSQL also notes that numeric operations can be slower than integer or floating-point operations, so use it when decimal correctness is worth that trade-off.
#1 Best Overall
How do numeric, double precision, and money compare?
| Type | Decimal behavior and scale | Locale and currency behavior | Practical guidance |
|---|---|---|---|
numeric(p,s) |
Exact where possible; precision and scale are chosen in the column declaration. | The type does not itself present a locale-formatted currency. | Use as the default for prices requiring exact decimal semantics. |
double precision |
Inexact binary floating point; it does not provide a fixed number of decimal places. | The type does not itself present a locale-formatted currency. | Avoid for stored monetary amounts when exact decimal results matter. |
money |
Fixed fractional precision dependent on lc_monetary. |
Output is locale-sensitive; it does not replace an application-level currency identifier. | Consider only if its scale, formatting, and arithmetic behavior suit the application. |
PostgreSQL 18 describes numeric as especially recommended for monetary amounts requiring exactness, double precision as inexact, and money as a currency amount with fixed fractional precision. See the official Numeric Types documentation and Monetary Types documentation.
Why shouldn’t you use double precision for money?
double precision stores an eight-byte binary floating-point value. Many decimal fractions cannot be represented exactly in binary, so a stored or calculated value can be an approximation of the decimal amount you intended. PostgreSQL documents at least 15 decimal digits of precision on currently supported platforms, but that does not make decimal currency arithmetic exact.
Rank #2
This can matter when you compare amounts for equality or accumulate many calculations: the result may differ slightly from the decimal figure expected by an application. Formatting a floating-point result to two decimal places changes its display, not the underlying representation or arithmetic. Use numeric when exact decimal semantics are required rather than relying on display rounding.
Free tools Windows power users keep installed
One-click scans. No signup required.
Should you use numeric or money for currency in Postgres?
money is a built-in amount type, but its fractional precision and output depend on the database’s lc_monetary setting. That makes it a specialized fit, not an automatic improvement over numeric. PostgreSQL warns that locale-sensitive output can make loading money data into a database with a different lc_monetary setting problematic.
The documented money range assumes two fractional digits: -92233720368547758.08 through +92233720368547758.07. This is a type limit, not a recommended business range, and the fractional-precision setting remains relevant.
Know how money division behaves
- Dividing a
moneyvalue by an integer truncates toward zero. - Dividing one
moneyvalue by another returnsdouble precision. - For rounded division, PostgreSQL recommends casting to
numericbefore dividing and casting back afterward, rather than risking precision loss through a floating-point divisor.
Neither the type nor the database’s locale setting defines the application’s tax, accounting, or rounding policy. Set that policy explicitly and apply rounding at the appropriate calculation boundary.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How should you model prices in multiple currencies?
Store the amount and currency identity as separate pieces of data. A numeric value such as 12.50 does not say whether it means USD, EUR, or another currency. Likewise, money formatting reflects locale settings; it is not a durable currency code for application logic or data exchange.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Use an explicit currency code or currency identity alongside the amount when the application handles multiple currencies. Define the allowed amount range, scale, conversion rules, and rounding behavior for the application rather than assuming that a database type supplies those policies.
Quick Recap
When is each type the right choice?
- Choose
numeric(p,s)for prices, fees, balances, or other monetary amounts where decimal exactness matters. Select the precision and scale to match valid values and calculation needs. - Choose
double precisionfor measurements or calculations where approximate floating-point behavior is acceptable, not ordinary stored prices requiring exact decimal results. - Choose
moneyonly if its fixed fractional precision, locale-sensitive display, and arithmetic—including division behavior—fit the database and application.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
