Numeric and decimal precision in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of numeric addition with different precisions

What is the output of the following SQL query in PostgreSQL?

SELECT CAST(1.2345 AS numeric(5,3)) + CAST(2.1 AS numeric(3,1)) AS result;

PostgreSQL

SELECT CAST(1.2345 AS numeric(5,3)) + CAST(2.1 AS numeric(3,1)) AS result;

A 3.335

B 3.3350

C 3.33

D 3.334

💡 Hint

Remember that numeric(p,s) means total digits p and s digits after decimal.

The first number is cast to numeric(5,3), so it is rounded to 3 decimal places: 1.235 (1.2345 rounds up). The second number is numeric(3,1), so 2.1. Adding 1.235 + 2.1 = 3.335. The result inherits precision from the operation and is displayed as 3.335.

📝 Syntax

intermediate

Identify the syntax error in numeric declaration

Which option contains a syntax error when declaring a numeric column in PostgreSQL?

A numeric(10,2)

B numeric(7,3)

C numeric(5,)

D numeric(4,0)

💡 Hint

Check if both precision and scale are specified correctly.

Option C has a missing scale value after the comma, which is invalid syntax. Both precision and scale must be specified as integers.

❓ optimization

advanced

Choosing numeric precision for performance

You need to store monetary values with up to 2 decimal places and values up to 999999.99. Which numeric type declaration is best for performance and storage efficiency in PostgreSQL?

A numeric(8,2)

B numeric(10,2)

C numeric(6,2)

D numeric(7,3)

💡 Hint

Consider total digits needed and decimal places.

To store values up to 999999.99, you need 6 digits before decimal and 2 after, total 8 digits. numeric(8,2) fits exactly and uses less storage than larger precision types.

🔧 Debug

advanced

Why does this numeric calculation produce unexpected result?

Given the query:

SELECT (CAST('0.1' AS numeric) + CAST('0.2' AS numeric))::float8 AS result;

Why might the result not be exactly 0.3?

A Because the string literals are not valid numeric values

B Because numeric type cannot represent decimal values accurately

C Because the addition operator is not supported for numeric types

D Because casting numeric to float8 introduces floating-point precision errors

💡 Hint

Think about differences between numeric and float types.

Numeric type stores exact decimal values. Casting to float8 converts to floating-point which can introduce small precision errors, causing result to be slightly off from 0.3.

🧠 Conceptual

expert

Understanding numeric precision and scale limits

In PostgreSQL, what is the maximum precision allowed for the numeric type, and what happens if you try to declare numeric(131072,16383)?

A Maximum precision is unlimited; numeric(131072,16383) is allowed but inefficient

B Maximum precision is 1000; declaring numeric(131072,16383) causes an error

C Maximum precision is 131072; numeric(131072,16383) is valid

D Maximum precision is 16383; numeric(131072,16383) causes an error

💡 Hint

Check PostgreSQL documentation on numeric precision limits.

PostgreSQL limits numeric precision to 1000 digits. Declaring numeric(131072,16383) exceeds this and causes an error.