skip to content

For fares in PostgreSQL, why is the money type usually avoided, and what do numeric and double precision trade instead?

level: middleimportance: should knowfreq 46%

answer

  1. exact decimal versus binary approximation
  2. the locale decides the decimals
  3. a per-session locale setting
  4. no currency code at all
  5. ties rounded away from zero

basics

~10 s

PostgreSQL's money is an integer of minor units whose decimals and formatting come from lc_monetary, so it cannot carry several currencies safely. Use numeric for exact fares; double precision is fast but inexact.

solid answer

~40 s

`money` is an 8-byte integer count of the smallest currency unit, and how many fractional digits it has — and how it prints — comes from `lc_monetary`, which any session can change. It has no currency code, so a service selling fares in euros, yen and dinars cannot model them in one column, and the manual warns that loading `money` data into a database with a different `lc_monetary` may fail. `numeric(p,s)` is exact decimal arithmetic, rounds ties away from zero, and is much slower than integer or float maths; it is the type for fares, beside a currency-code column. `double precision` is IEEE 754 binary floating point: fast, about 15 significant decimal digits, but values like 0.1 are approximations, so sums of prices drift. Keep it for measurements, not money.

code

sql · 14 lines
sql
CREATE TABLE fare (
  fare_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  currency text          NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
  amount   numeric(12,3) NOT NULL CHECK (amount >= 0)
);

SELECT 0.1::float8 + 0.2::float8 = 0.3::float8;   -- false
SELECT 0.1::numeric + 0.2::numeric = 0.3;          -- true
SELECT round(2.5::numeric), round(2.5::float8);    -- 3 | 2

SET lc_monetary = 'en_US.UTF-8';
SELECT 1234.5::money;                              -- $1,234.50
SELECT '10.00'::money / 3;                         -- $3.33 (truncated)
SELECT '10.00'::money / '4.00'::money;             -- 2.5 (double precision)

go deeper

for a junior

Recall that numeric is the exact type for money, double precision is approximate, and money is a PostgreSQL-specific type most teams avoid.

for a middle

Explain money as a scaled integer interpreted by lc_monetary, why that breaks multi-currency data and restores, and how numeric and float round ties differently.

for a senior

Show the production consequences: drifting float sums in reconciliation, a dump that will not load under another locale, and where explicit rounding belongs in a fare pipeline.

for a principal

Decide the house convention for amounts across services — numeric with a currency column or integer minor units — and how rounding rules are owned and tested.

## Three ways to hold a non-integer PostgreSQL offers three families for amounts with a fractional part, and they differ in what they promise: - **`numeric`** (alias `decimal`) — exact decimal arithmetic with user-chosen precision and scale; - **`real` and `double precision`** — IEEE 754 binary floating point, inexact by design; - **`money`** — a PostgreSQL-specific type that is really a scaled 64-bit integer. The engine-neutral argument against floats for money is well known. What PostgreSQL adds is the third option, which looks purpose-built for prices and is the one interviewers expect you to reject with reasons. ## numeric: exact and slow - `numeric(p, s)` fixes the **precision** (total significant digits) and **scale** (digits after the point); input with more decimals than `s` is rounded to `s`, and too many integer digits raises `numeric field overflow`. - Unconstrained `numeric` stores whatever it is given, up to 131072 digits before the point and 16383 after; a declared precision may be at most 1000. - Storage is variable: roughly two bytes per four decimal digits plus a small header, so `numeric` amounts are compact even though the arithmetic is slow. - Aggregates stay exact: `sum` and `avg` over `numeric` return `numeric`, so a day's revenue reconciles to the last minor unit. - Addition, subtraction and multiplication are **exact**. - Rounding sends **ties away from zero**: `round(2.5::numeric)` is 3. - It accepts `NaN`, `Infinity` and `-Infinity`, which a `CHECK` can exclude. - The cost is speed: the manual calls numeric calculations *very slow* compared with integers and floats. For a fare table that is almost never the bottleneck. ## double precision: fast and approximate - `double precision` is 8 bytes with about **15 decimal digits** of precision. - Values such as 0.1 and 0.2 have no exact binary representation, so `0.1::float8 + 0.2::float8 = 0.3::float8` is false. - Rounding on most machines sends **ties to even**: `round(2.5::float8)` is 2, which disagrees with `numeric` on the same input. - Output is the shortest text that reads back to the exact stored value, controlled by `extra_float_digits` (default 1). It is the right type for sensor readings, distances and statistics, not for fares that must reconcile to the cent. ## money: an integer the locale interprets Internally `money` is a 64-bit integer. Its meaning comes from the session's **`lc_monetary`** setting: 1. The locale's fractional digits decide how input is scaled and how output is printed; with two digits the range is about ±92 quadrillion. 2. Output is locale-formatted text such as `$1,234.50`, and input accepts that format. 3. `lc_monetary` is a session-settable parameter (initdb seeds it in `postgresql.conf`), so two sessions can read the same stored integer differently. 4. The manual warns that restoring a dump into a database with a different `lc_monetary` may fail; it advises matching the setting first. 5. There is **no currency code**: one column cannot say whether a row is yen or euro. 6. Arithmetic has its own rules: money divided by an integer **truncates** toward zero, money divided by money returns `double precision`, and converting from a float must go through `numeric`. For a single-currency, single-locale ledger it works. For a fare service quoting in several currencies, it is the wrong abstraction. ## Side by side | | `numeric(p,s)` | `double precision` | `money` | |---|---|---|---| | Exactness | exact decimal | approximate binary | exact integer of minor units | | Decimals | declared scale | about 15 significant digits | from `lc_monetary` | | Currency awareness | none (add a column) | none | implied by locale only | | Speed | slowest | fast | fast | | Tie rounding | away from zero | to even | truncation on integer division | | Fits fares | yes | no | single-currency only | ## What the fare service stores 1. `amount numeric(12,3)` — three decimals covers currencies with three minor digits; JPY rows simply carry zeros. 2. `currency text` with a three-letter `CHECK`, so every amount says what it is. 3. Arithmetic in `numeric`, rounding explicitly with `round(x, 2)` at the points the business defines. 4. An alternative that also works: `bigint` minor units plus the currency's exponent, with all rounding done deliberately.

  • Why not store fares as bigint minor units instead of numeric?
    It is exact and fast, and many payment systems do it. The price is bookkeeping: each currency's exponent (0 for JPY, 2 for EUR, 3 for KWD) must be known wherever amounts are shown or converted, and every division, tax or proration needs explicit rounding in code. `numeric(p,s)` keeps the decimal point in the database and rounds to the scale on assignment.
  • In PostgreSQL, what happens when 1234567.891 is stored into a numeric(8,2) column?
    Rounding to the scale happens first, giving 1234567.89, but that needs seven integer digits and `numeric(8,2)` allows only six, so the insert fails with `numeric field overflow` (SQLSTATE `22003`). A value such as 123.456 would instead be stored as 123.46, rounded half away from zero.

  • money stores a currency code, so it handles multi-currency fares.
  • money's fractional digits are fixed at two regardless of settings.
  • double precision is fine for fares if you round on display.
  • numeric arithmetic is as fast as integer arithmetic.
  • numeric rounds ties to even, like banker's rounding.