Skip to main content

numeric

Learning Focus

Use this lesson to choose PostgreSQL numeric(p,s) for exact values and avoid floating-point rounding in financial calculations.

numeric Overview​

  • Exact, arbitrary precision (within configured precision)
  • Use numeric(p,s) to set precision and scale (e.g., numeric(12,2))
  • Best for: money-like values, measurements requiring exact decimals

Example​

CREATE TABLE invoices (
  invoice_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);

Performance Notes​

  • numeric is slower than integer arithmetic.
  • For very high volume payments, some systems store cents as bigint and format in the application.

Common Pitfalls​

PitfallConsequencePrevention
Using float types for moneyRounding errorsUse numeric(p,s)

What's Next​