Menu

PostgreSQL NUMERIC: Precision, Scale, and Examples

Use PostgreSQL NUMERIC or DECIMAL for exact decimal values. Learn precision, scale, rounding, range limits, and when to choose integer or floating-point types.

Updated on

Use PostgreSQL NUMERIC (equivalent to DECIMAL) when decimal values and calculations must be exact, such as monetary amounts. NUMERIC is slower than integer and floating-point arithmetic, so choose a type for the values and accuracy your application needs.

Requirement PostgreSQL type Why
Whole-number counts or identifiers SMALLINT, INTEGER, or BIGINT Exact integers with fixed storage and range.
Exact decimal values, such as prices NUMERIC or DECIMAL Exact decimal storage and arithmetic where possible.
Approximate measurements or scientific calculations REAL or DOUBLE PRECISION Faster binary floating-point arithmetic with approximate results.

NUMERIC syntax

NUMERIC(precision, scale)
NUMERIC(precision)
NUMERIC
  • Precision is the total number of significant digits, counting digits on both sides of the decimal point.
  • Scale is the number of digits to the right of the decimal point.
  • NUMERIC(precision) has scale 0.
  • NUMERIC without precision or scale is unconstrained: PostgreSQL stores the supplied scale rather than rounding values to a declared scale.

Explicit precision can be at most 1,000. PostgreSQL supports scales from -1,000 to 1,000. Since PostgreSQL 15, scale may be negative or greater than precision; those declarations may not be portable to other database systems.

With a negative scale, PostgreSQL rounds to the left of the decimal point. A scale greater than precision is useful for values that are always smaller than 1:

SELECT
    12567::numeric(2, -3) AS nearest_thousand,
    0.00123::numeric(3, 5) AS small_fraction;

 nearest_thousand | small_fraction
------------------+----------------
            13000 |        0.00123

For example, 23.5141 has precision 6 and scale 4. A NUMERIC(5,2) column can hold values from -999.99 to 999.99.

Rounding and range

When an input has more fractional digits than the column’s scale, PostgreSQL rounds it to that scale. It then checks whether the rounded value fits the remaining integer digits (precision - scale). For example:

CREATE TABLE invoices (
    invoice_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount numeric(5, 2) NOT NULL
);

INSERT INTO invoices (amount) VALUES (234.456)
RETURNING amount;

 amount
--------
 234.46

NUMERIC(5,2) allows at most three digits to the left of the decimal point. Inserting 1234.56 raises a numeric field overflow error; it cannot fit even though PostgreSQL can round fractional digits.

Unconstrained values and special values

An unconstrained NUMERIC column can hold very large values, up to PostgreSQL’s implementation limits: 131,072 digits before the decimal point and 16,383 after it. It can also store Infinity, -Infinity, and NaN. The infinity values cannot fit in a column with a declared precision, while NaN is supported by constrained and unconstrained NUMERIC columns.

Further reading