Why should I avoid the money type in Postgres?

money type bound to server locale

Note: this works and blocks nothing, but a performance regression is likely.

What happens

The money type's meaning depends on the server's lc_monetary locale: no currency is stored, fractional precision is fixed, and dumping/restoring across locales reinterprets the values. numeric plus an explicit currency column is the boring, correct answer.

Why it is dangerous on a populated table

Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.

Fires on

CREATE TABLE orders (total money);

The safe pattern

Store amounts as numeric (or integer minor units) with an explicit currency column. Exact arithmetic, no locale dependence, dumps and restores mean the same thing everywhere.

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total numeric(12,2) NOT NULL,
  currency text NOT NULL DEFAULT 'EUR'
);

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

money

ALTER TABLE orders ADD COLUMN shipping_cost money;

Stays silent (1)

numeric

ALTER TABLE orders ADD COLUMN shipping_cost numeric(12,2);

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV041 BV043 →