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.

Practice

1. What does the scale represent in a NUMERIC(10, 3) data type in PostgreSQL?

easy

A. The number of digits after the decimal point

B. The total number of digits allowed

C. The number of digits before the decimal point

D. The maximum value allowed

Solution

1. Step 1: Understand NUMERIC precision and scale

   In NUMERIC(precision, scale), precision is total digits, scale is digits after decimal.

2. Step 2: Apply to NUMERIC(10, 3)

   Here, scale = 3 means 3 digits after decimal point.

3. Final Answer:

   The number of digits after the decimal point -> Option A

4. Quick Check:

   Scale = digits after decimal [OK]

Hint: Scale is digits after decimal point in NUMERIC [OK]

Common Mistakes:

• Confusing scale with precision
• Thinking scale is digits before decimal
• Assuming scale limits total digits

2. Which of the following is the correct syntax to define a column named price with 7 digits total and 2 digits after the decimal in PostgreSQL?

easy

A. price DECIMAL(7)

B. price NUMERIC(2,7)

C. price DECIMAL(2,7)

D. price NUMERIC(7,2)

Solution

1. Step 1: Recall NUMERIC/DECIMAL syntax

   Syntax is NUMERIC(precision, scale) where precision >= scale.

2. Step 2: Match precision=7 and scale=2

   price NUMERIC(7,2) uses NUMERIC(7,2) which means 7 total digits, 2 after decimal.

3. Final Answer:

   price NUMERIC(7,2) -> Option D

4. Quick Check:

   NUMERIC(precision, scale) = NUMERIC(7,2) [OK]

Hint: Precision first, scale second in NUMERIC/DECIMAL [OK]

Common Mistakes:

• Swapping precision and scale
• Using scale larger than precision
• Omitting scale when needed

3. Given the table products(price NUMERIC(5,2)), what will be the stored value after inserting INSERT INTO products(price) VALUES (123.4567);?

medium

A. 123.45

B. 123.46

C. 123.4567

D. Error: value too precise

Solution

1. Step 1: Understand NUMERIC(5,2) limits

   Precision 5 means total 5 digits, scale 2 means 2 digits after decimal.

2. Step 2: Round input 123.4567 to 2 decimal places

   123.4567 rounds to 123.46 to fit scale 2.

3. Final Answer:

   123.46 -> Option B

4. Quick Check:

   Rounded to 2 decimals = 123.46 [OK]

Hint: NUMERIC rounds to scale digits on insert [OK]

Common Mistakes:

• Expecting truncation instead of rounding
• Assuming error on extra decimals
• Ignoring precision limits

4. You defined a column as amount NUMERIC(4,2). You try to insert 999.99 but get an error. What is the problem?

medium

A. NUMERIC cannot store decimal values

B. The scale 2 is too small for the value

C. The value exceeds the total precision of 4 digits

D. Syntax error in column definition

Solution

1. Step 1: Calculate digits in 999.99

   999.99 has 5 digits total (3 before decimal + 2 after).

2. Step 2: Compare with NUMERIC(4,2)

   NUMERIC(4,2) allows only 4 digits total, so 999.99 is too large.

3. Final Answer:

   The value exceeds the total precision of 4 digits -> Option C

4. Quick Check:

   Value digits > precision causes error [OK]

Hint: Total digits must fit precision, not just decimals [OK]

Common Mistakes:

• Thinking scale limits integer part
• Believing NUMERIC disallows decimals
• Ignoring precision limits

5. You want to store monetary values up to 999999.999 with exact precision in PostgreSQL. Which column definition is best to avoid rounding errors and allow this range?

hard

A. amount NUMERIC(9,3)

B. amount NUMERIC(6,3)

C. amount DECIMAL(9,2)

D. amount FLOAT(9)

Solution

1. Step 1: Determine needed precision and scale

   Value 999999.999 has 6 digits before decimal, 3 after, total 9 digits.

2. Step 2: Choose NUMERIC with precision 9 and scale 3

   NUMERIC(9,3) stores exactly 9 digits total with 3 decimals, matching requirement.

3. Step 3: Avoid FLOAT for exact money values

   FLOAT is approximate, may cause rounding errors.

4. Final Answer:

   amount NUMERIC(9,3) -> Option A

5. Quick Check:

   Exact money storage needs NUMERIC(9,3) [OK]

Hint: Use NUMERIC with precision = digits total, scale = decimals [OK]

Common Mistakes:

• Using FLOAT for money causing rounding errors
• Choosing too small precision or scale
• Confusing DECIMAL scale with precision