NUMERIC

The PostgreSQL NUMERIC type (also called DECIMAL) stores numbers with exact precision. It is the correct choice for financial data, quantities, or any value where floating-point approximation is unacceptable.

Syntax and precision/scale

column_name NUMERIC(precision, scale)

• precision: total number of significant digits
• scale: digits to the right of the decimal point

Example: NUMERIC(7, 2) stores up to 99999.99. If you omit both, NUMERIC defaults to up to 131,072 digits before and 16,383 after the decimal point.

NUMERIC, DECIMAL, and DEC are all synonyms:

price NUMERIC(5,2)
price DECIMAL(5,2)
price DEC(5,2)  -- all equivalent

Rounding and overflow behavior

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  price NUMERIC(5,2)
);

Inserting values with more decimal places than the scale causes automatic rounding:

INSERT INTO products (name, price)
VALUES ('Phone', 500.215), ('Tablet', 500.214);

SELECT * FROM products;

id |  name  | price
----+--------+--------
 1 | Phone  | 500.22
 2 | Tablet | 500.21

Exceeding the total precision raises an error:

INSERT INTO products (name, price) VALUES ('Phone', 123456.21);
-- ERROR: numeric field overflow
-- DETAIL: A field with precision 5, scale 2 must round to an absolute value less than 10^3.

NaN: not-a-number

NUMERIC supports the special value NaN (not-a-number). It must be quoted when used as a literal:

UPDATE products SET price = 'NaN' WHERE id = 1;

SELECT * FROM products ORDER BY price DESC;

id |  name  | price
----+--------+--------
 1 | Phone  |    NaN
 2 | Tablet | 500.21

In PostgreSQL, NaN is considered greater than all regular numbers and equal to itself — unlike IEEE 754 — so it sorts predictably and works in indexes.

Production tips

• Always use NUMERIC for monetary values — never FLOAT or DOUBLE PRECISION, which can introduce rounding errors in financial calculations.
• Arithmetic on NUMERIC is slower than on integer or float types. For non-financial data where approximation is acceptable, prefer DOUBLE PRECISION.
• Use NUMERIC(p, s) with explicit precision and scale for columns that interface with external systems to prevent unexpected precision changes.
• To store currency, a common pattern is NUMERIC(19, 4) — 15 digits before the decimal and 4 after, providing enough room for most real-world monetary values.

Reference: PostgreSQL documentation — Numeric Types.