Why should you use numeric instead of float or real for money in PostgreSQL?
float and real are binary approximations that cannot represent values like 0.1 exactly, so sums drift. numeric stores exact decimal digits, which is what money needs. Many teams store paise as a bigint instead.
Explain it simply
Try writing one third as a decimal: 0.3333... never ends. Computers have the same trouble with 0.1 in binary. real and double precision store the closest binary fraction, so tiny errors creep in and can show up after many additions. For a fee of Rs 0.10 charged a million times, you do not want a "close enough" total.
numeric(precision, scale) keeps exact decimal digits. numeric(12,2) allows 12 digits in total, 2 after the point.
Example
SELECT 0.1::float8 + 0.2::float8 = 0.3::float8; -- false
SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric; -- true
CREATE TABLE payments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric(12,2) NOT NULL CHECK (amount > 0)
);numeric is slower than float, which is fine for money and wrong for scientific data. Some teams skip decimals entirely and store the amount in paise as a bigint.
What to say in the interview
"float types are approximate, so I use numeric(12,2) for rupee amounts, or a bigint of paise. float is fine for measurements where tiny error is acceptable and speed matters. I also avoid the money type because its formatting depends on locale settings."
Common mistakes
- Using real for prices and then finding totals off by a paisa.