Numeric and decimal precision in PostgreSQL - Step-by-Step Execution

Concept Flow - Numeric and decimal precision

Define numeric/decimal column with precision

↓

Insert value into column

↓

Check if value fits precision and scale

↓

Store value

↓

Retrieve value with precision

This flow shows how PostgreSQL handles numeric/decimal values with defined precision and scale during insert and retrieval.

Execution Sample

PostgreSQL

CREATE TABLE prices (
  price NUMERIC(5,2)
);

INSERT INTO prices VALUES (123.456);
SELECT price FROM prices;

Create a table with a numeric column of precision 5 and scale 2, insert a value with more decimals, then select it.

Execution Table

Step	Action	Input Value	Precision Check	Stored Value	Notes
1	Create table	N/A	N/A	Table with price NUMERIC(5,2)	Defines max 5 digits total, 2 after decimal
2	Insert value	123.456	123.456 fits? Max digits=5, scale=2	123.46	Value rounded to 2 decimal places
3	Select value	N/A	N/A	123.46	Retrieved value shows rounded number
4	Insert large value	12345.67	Digits=7 > 5	Error	Value too large for defined precision

💡 Execution stops after error on inserting value exceeding precision

Variable Tracker

Variable	Start	After Insert 1	After Insert 2	Final
price	NULL	123.46	Error (no change)	123.46

Key Moments - 2 Insights

Why does 123.456 become 123.46 when inserted?

Because the column is defined with scale 2, PostgreSQL rounds the value to 2 decimal places as shown in execution_table row 2.

What happens if the number has more digits than precision allows?

PostgreSQL raises an error and does not store the value, as shown in execution_table row 4.

Visual Quiz - 3 Questions

Look at the execution table, what is the stored value after inserting 123.456?

A 123.456

B 123.46

C 123.45

D Error

💡 Hint

Check execution_table row 2 under Stored Value column

At which step does an error occur due to precision overflow?

A Step 3

B Step 2

C Step 4

D Step 1

💡 Hint

See execution_table row 4 Notes column

If the scale was changed to 3, how would the stored value for 123.456 change?

A It would store 123.456 exactly

B It would round to 123.46

C It would cause an error

D It would store 123.45

💡 Hint

Scale controls decimal places, see concept_flow and execution_table row 2

Concept Snapshot

NUMERIC(p,s) or DECIMAL(p,s) defines a number with p total digits and s digits after decimal.
Values inserted are rounded to scale s.
If value exceeds precision p, an error occurs.
Use for exact decimal storage like money.
Example: NUMERIC(5,2) stores up to 999.99.

Full Transcript

This visual execution trace shows how PostgreSQL handles numeric and decimal precision. First, a table is created with a numeric column defined as NUMERIC(5,2), meaning it can store numbers with up to 5 digits total and 2 digits after the decimal point. When inserting the value 123.456, PostgreSQL rounds it to 123.46 to fit the scale of 2 decimal places. Selecting the value returns 123.46. If a value like 12345.67 is inserted, which has 7 digits, it exceeds the precision and causes an error, so it is not stored. The variable tracker shows the price value changing from NULL to 123.46 after the first insert, and no change after the failed insert. Key moments clarify why rounding happens and what causes errors. The quiz questions test understanding of rounding, error steps, and scale effects. The snapshot summarizes the key rules for numeric precision and scale in PostgreSQL.

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