Numeric and decimal precision in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a column with numeric precision of 5 and scale of 2.

PostgreSQL

CREATE TABLE products (price NUMERIC([1]));

Drag options to blanks, or click blank then click option'

A5, 2

B10, 5

C3, 1

D7, 3

Attempts:

3 left

💡 Hint

Common Mistakes

Using only one number inside NUMERIC() without scale.

Swapping precision and scale values.

The NUMERIC(5, 2) means the number can have up to 5 digits in total, with 2 digits after the decimal point.

2fill in blank

medium

Complete the code to insert a value with two decimal places into the price column.

PostgreSQL

INSERT INTO products (price) VALUES ([1]);

Drag options to blanks, or click blank then click option'

A99.99

B123.456

C100

D12.3456

Attempts:

3 left

💡 Hint

Common Mistakes

Inserting values with more than two decimal places.

Inserting integer values without decimal places.

Since the price column has scale 2, values must have at most two digits after the decimal point.

3fill in blank

hard

Fix the error in the SELECT statement to round the price to 1 decimal place.

PostgreSQL

SELECT ROUND(price, [1]) FROM products;

Drag options to blanks, or click blank then click option'

A3

B0

C-1

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 rounds to whole number, not one decimal place.

Using negative numbers rounds to tens or hundreds.

ROUND(price, 1) rounds the price to one decimal place.

4fill in blank

hard

Fill both blanks to create a table with a decimal column and insert a value with correct precision.

PostgreSQL

CREATE TABLE sales (amount DECIMAL([1], [2]));
INSERT INTO sales (amount) VALUES (123.45);

Drag options to blanks, or click blank then click option'

A6

B2

C4

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using scale larger than precision.

Choosing precision smaller than number of digits in value.

DECIMAL(6, 2) means 6 total digits with 2 after the decimal point, so 123.45 fits.

5fill in blank

hard

Fill all three blanks to select prices rounded to 0 decimal places and filter prices greater than 100.

PostgreSQL

SELECT ROUND(price, [1]) AS rounded_price FROM products WHERE price [2] [3];

Drag options to blanks, or click blank then click option'

A1

B>

C100

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 instead of 0 for rounding decimal places.

Using wrong comparison operator in WHERE clause.

ROUND(price, 0) rounds to whole numbers. The WHERE clause filters prices greater than 100.

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