Numeric and decimal precision in PostgreSQL

Introduction

We use numeric and decimal precision to store numbers exactly, especially when we need to keep the right number of digits after the decimal point, like money or measurements.

When storing prices in a shopping app to avoid rounding errors.

When recording measurements that need exact decimal places, like weight or length.

When calculating taxes where exact decimal values are important.

When you want to control how many digits appear after the decimal point.

When you want to avoid floating-point rounding issues in financial data.

Syntax

PostgreSQL

NUMERIC(precision, scale)
DECIMAL(precision, scale)

precision is the total number of digits allowed (both sides of the decimal).

scale is how many digits are allowed after the decimal point.

Examples

Allows numbers with up to 5 digits total, with 2 digits after the decimal point (e.g., 123.45).

PostgreSQL

NUMERIC(5, 2)

Allows numbers with up to 10 digits total, with 4 digits after the decimal point (e.g., 123456.7890).

PostgreSQL

DECIMAL(10, 4)

Without precision and scale, it stores numbers with any number of digits and decimals.

PostgreSQL

NUMERIC

Sample Program

This creates a table with a price column that stores numbers with up to 6 digits total and 2 digits after the decimal. It inserts three prices and then shows all rows.

PostgreSQL

CREATE TABLE prices (
  item_id SERIAL PRIMARY KEY,
  price NUMERIC(6, 2)
);

INSERT INTO prices (price) VALUES (1234.56), (78.9), (0.123);

SELECT * FROM prices;

Important Notes

If you insert a number with more decimal digits than the scale, PostgreSQL rounds it.

If the number is too big for the precision, PostgreSQL will give an error.

NUMERIC and DECIMAL are the same in PostgreSQL.

Summary

Use NUMERIC or DECIMAL to store exact numbers with control over digits.

Precision is total digits; scale is digits after the decimal point.

Helps avoid rounding errors in money and measurements.