How to Store Money in PostgreSQL: numeric vs the money Type

Store money in PostgreSQL as numeric (the SQL standard also calls it decimal), an arbitrary-precision exact decimal type. The practical default is numeric(19,4): four digits after the decimal point, fifteen in front, exact to the cent and beyond with no rounding drift. The high-volume alternative is to store the integer count of minor units (cents) in a bigint. Either way you store the currency code separately in a char(3) column, because an amount with no currency attached is a bug waiting to surface. Two things you should not reach for: real/double precision (binary floats that drift), and PostgreSQL's own money type (which looks tempting but ties itself to the server's locale setting).

Short answer: amount numeric(19,4) NOT NULL plus currency char(3) NOT NULL for almost every application. The whole job in Postgres is "use numeric, not money." Unlike MySQL, where the decision is simpler because there is no money type at all, Postgres ships a dedicated money type, and the right move is to walk past it. Switch the amount to bigint integer cents only when you are running a high-throughput ledger and want integer arithmetic end to end.

Why numeric is the right type, and money is not

PostgreSQL's documentation is direct about it: the numeric type "is especially recommended for storing monetary amounts and other quantities where exactness is required." That is the whole recommendation in one line. numeric stores values as exact decimal digits, not as a binary approximation, so 0.1 + 0.2 is exactly 0.3 and a sum() over a hundred thousand invoices lands on the exact total.

The money type exists and is genuinely tempting: it is a fixed 8 bytes, it is fast, and it reads naturally. The problem is locale coupling. The money type's fractional precision is determined by the database's lc_monetary setting, and its input and output formatting depend on that same locale. So the value $1,000.00 on a server set to en_US is parsed and rendered differently on a server set to a locale that uses a comma decimal separator or a different currency symbol. The Postgres manual warns that loading money data into a database with a different lc_monetary "might not work," and tells you to match lc_monetary before restoring a dump. That is a portability trap baked into the type itself: the same bytes mean different things depending on a server-level GUC you may not control.

The other catch is that money carries no currency identity. The symbol it prints is whatever lc_monetary says, not a fact stored per row, so a money column cannot represent a genuinely multi-currency table. You end up needing a separate currency column anyway, at which point money has bought you nothing over numeric except a locale dependency you did not want. The community consensus, and my own default, is to use numeric for money and leave money alone.

Why you must never use real or double precision

real and double precision are IEEE 754 binary floating-point types, the same family as MySQL's FLOAT and DOUBLE. They store numbers as a binary mantissa and exponent, which is fine for measurements where a tiny relative error is acceptable and wrong for money where every cent must be exact.

Most decimal fractions have no exact binary representation. 0.1 in binary is a repeating fraction, so the database stores the nearest representable value, which is almost but not exactly 0.1, and the error compounds as you add those almost-values together. The Postgres docs put it plainly: floating point is "inexact," meaning some values "are stored as approximations, so that storing and retrieving a value might show slight discrepancies," and "if you require exact storage and calculations (such as for monetary amounts), use the numeric type instead." A double precision money column gives you a sum() that is off by a few cents over enough rows and a WHERE price = 19.99 that matches nothing because the stored value was never exactly 19.99. Keep floats off money entirely.

numeric(19,4), the recommended default

numeric(precision, scale) lets you pin the exactness to a declared shape. The precision is the total count of significant digits and the scale is how many of them sit after the decimal point. numeric(19,4) means 19 total digits, 4 after the point and 15 before it, which holds any realistic total to sub-cent precision. The four decimal places matter for FX amounts, per-unit rates, fuel pricing, and interest accrual that quote to four or more places.

A subtlety worth knowing: when you specify a scale, Postgres rounds the input to that scale on store. numeric(19,4) coerces 19.999999 to 19.9999. Bare numeric with no precision or scale is an "unconstrained numeric" that stores whatever exact value you hand it, to any length up to the implementation limit. For money you generally want the constrained form, because pinning the scale in the schema means every row shares the same precision and a stray high-precision value cannot sneak in. If you only ever deal with whole-cent amounts, numeric(12,2) is a lighter, perfectly correct choice. I still default to numeric(19,4) on anything that might touch FX or sub-cent rates, because widening later is an ALTER TABLE I would rather skip.

The reason numeric is safe where double precision is not: it is decimal, not binary. 0.1 is stored as the digit 1 at scale 1, exactly, so the arithmetic is exact and a hundred thousand of them sum to the right total.

bigint integer cents, for high-volume ledgers

The other exact approach skips decimals and stores the integer count of minor units. A US dollar's minor unit is the cent, so $19.99 becomes the integer 1999 in a bigint, and you divide by 100 to display. It is exact for the same reason integers are exact (no fractional rounding at all) and it is fast, because both the database and the application do plain integer arithmetic with no decimal machinery in the path. The speed gap is real: aggregating over a numeric column runs meaningfully slower than over bigint, because numeric arithmetic is software decimal math while bigint rides the CPU's native integer ops. On a normal application that difference never shows up; on a ledger summing millions of rows per query it does.

The trade is that you carry two facts in the application rather than the column: the scale (minor units per major unit, 2 for dollars and euros, 0 for yen) and the currency. You multiply by 100 on input, divide by 100 on display, and keep that consistent everywhere. bigint holds up to about 9.2 quintillion, so even counting cents you have room for trillions. I reach for integer cents on a high-throughput payments or accounting ledger where every byte of row width and every bit of arithmetic speed counts, and where the team is disciplined about the scale convention. For a normal application, numeric(19,4) is less error-prone because the scale lives in the schema, not in everyone's head.

Comparison: real/double vs money vs numeric(19,4) vs bigint cents

Two rows are correct (numeric and bigint cents); the choice between them is ergonomics versus throughput, not accuracy. The float row is here only to be unambiguous: never for money. The money row is here because Postgres ships it and you will be tempted, so the table states plainly why to skip it.

Always store the currency code separately

An amount is meaningless without a currency. 1000 could be a thousand dollars or a thousand yen, and those differ by two orders of magnitude. Store the currency in its own char(3) column holding the ISO 4217 three-letter code: USD, EUR, GBP, JPY. char(3) is exactly the right width (the codes are always three uppercase letters) and fixed-length. This is also the gap the money type cannot fill: it prints whatever lc_monetary dictates rather than a per-row fact, so a real multi-currency table needs an explicit currency column regardless.

Keeping currency in its own column also stops a subtle bug: if you ever sum() a mixed-currency table without grouping by currency, you add dollars to euros and get nonsense. With a currency column you GROUP BY currency, and the schema makes the multi-currency reality visible instead of hiding it.

A worked schema

A minimal payments table using the recommended default. The amount is exact numeric(19,4), the currency a required char(3), both NOT NULL.

CREATE TABLE payments (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   bigint NOT NULL,
  amount     numeric(19,4) NOT NULL,
  currency   char(3) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_payments_order ON payments (order_id);

INSERT INTO payments (order_id, amount, currency) VALUES
  (1, 19.99, 'USD'),
  (1,  5.00, 'USD'),
  (1,  0.10, 'USD'),
  (1,  0.20, 'USD');

-- exact: this returns 25.2900, not 25.28999...
SELECT currency, sum(amount) AS total
FROM payments
WHERE order_id = 1
GROUP BY currency;

That sum() returns exactly 25.2900. The 0.10 and 0.20 rows add to exactly 0.30, because numeric is decimal and never went near a binary mantissa. Run the same data through a double precision column and the total can land a fraction off, which is the whole reason the column type matters. Postgres keeps sum() over a numeric column exact, so the aggregate is as trustworthy as the stored values.

When you genuinely need to drop precision (you can only charge a customer to the cent, not the hundredth of a cent), round at a well-defined point with round():

SELECT round(sum(amount), 2) AS charge_amount
FROM payments
WHERE order_id = 1;

The rule is to keep full precision through the calculation and round once, deliberately, at the moment a real-world value is produced (the line you charge, the figure you print on an invoice). Rounding early and repeatedly accumulates its own error. And do the arithmetic itself in the database's exact types or in integer minor units, never by pulling amounts into application floats, which reintroduces exactly the drift you used numeric to avoid.

Keep floats out of the application layer too

Picking the right column type only gets you halfway. If your application reads a numeric out of Postgres and parses it into a native float, you have thrown away the exactness at the boundary. Carry money through your code in an exact type:

• Python: use decimal.Decimal, constructed from the string, not from a float literal. psycopg already hands you Decimal objects for numeric columns, which is what you want.
• Node.js / JavaScript: JavaScript has only IEEE 754 numbers. The node-postgres driver returns numeric as a string by design, precisely so it does not silently lose precision. Either keep money in integer cents (plain integer math, safe up to Number.MAX_SAFE_INTEGER) or feed that string into a decimal library like decimal.js or big.js. Never add two money amounts as plain JS numbers.
• Java: use BigDecimal, which the JDBC driver maps numeric to directly. Never double.

The principle is one sentence: money is exact from the column, through the query, into the application type, and back, with floats nowhere on the path.

What to do next

• For the same decision on the other major open-source database, see storing money and currency in MySQL. MySQL has no money type, so the choice is just exact DECIMAL versus integer cents.
• On the document side, the same "never use a float, decide between Decimal128 and integer minor units" tradeoff plays out in storing money in MongoDB.

FAQ

See also

• Storing money and currency in MySQL covers the same decision on MySQL, where there is no money type and the choice is exact DECIMAL versus integer cents.
• Storing money in MongoDB walks the document-database version: Decimal128 versus integer minor units, with floats off the path either way.
• For another PostgreSQL data-type call, see choosing between json and jsonb in PostgreSQL.
• Storing UUIDs in PostgreSQL is the companion guide for primary keys, including gen_random_uuid().
• Need a local instance to try the schema above? Running PostgreSQL in Docker gets you a throwaway server in one command.
• For a cross-database view of column widths and storage, the MySQL field types and sizes reference is a useful companion.