Numeric and decimal precision in PostgreSQL - Mini Project: Build & Apply

Working with Numeric and Decimal Precision in PostgreSQL

📖 Scenario: You are managing a small online store database. You need to store product prices accurately, including cents, and perform calculations that require precise decimal values.

🎯 Goal: Create a PostgreSQL table to store product prices using numeric types with specific precision and scale. Insert sample data, then write a query to select prices with correct decimal precision.

📋 What You'll Learn

Create a table named products with columns id (integer) and price (numeric with precision 8 and scale 2).

Insert three products with prices: 1234.56, 78.9, and 0.123.

Write a SELECT query to retrieve all products showing prices with two decimal places.

💡 Why This Matters

🌍 Real World

Storing prices and financial data accurately is critical in e-commerce and accounting systems to avoid rounding errors and maintain data integrity.

💼 Career

Database developers and analysts often work with numeric data types and constraints to ensure correct calculations and valid data in business applications.

1

Create the products table with numeric price column

Write a SQL statement to create a table called products with two columns: id as an integer primary key, and price as a numeric type with precision 8 and scale 2.

PostgreSQL

-- Create the products table with id and price columns
-- Your code here

Hint

Use NUMERIC(8, 2) to store prices with up to 8 digits total and 2 digits after the decimal point.

2

Insert sample product prices

Write SQL INSERT statements to add three products into the products table with these exact id and price values: (1, 1234.56), (2, 78.9), and (3, 0.123).

PostgreSQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price NUMERIC(8, 2)
);
-- Insert three products with given ids and prices
-- Your code here

Hint

Use a single INSERT statement with multiple rows for simplicity.

3

Select all products with prices formatted to two decimals

Write a SQL SELECT statement to retrieve id and price from the products table. Use the ROUND function on price to ensure it shows exactly two decimal places.

PostgreSQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price NUMERIC(8, 2)
);

INSERT INTO products (id, price) VALUES
(1, 1234.56),
(2, 78.9),
(3, 0.123);

-- Select id and price rounded to 2 decimals
-- Your code here

Hint

The ROUND function takes two arguments: the column and the number of decimal places.

4

Add a constraint to ensure price is non-negative

Alter the products table to add a CHECK constraint named price_non_negative that ensures the price column is always greater than or equal to zero.

PostgreSQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price NUMERIC(8, 2)
);

INSERT INTO products (id, price) VALUES
(1, 1234.56),
(2, 78.9),
(3, 0.123);

SELECT id, ROUND(price, 2) AS price FROM products;

-- Add a CHECK constraint to ensure price is >= 0
-- Your code here

Hint

Use ALTER TABLE with ADD CONSTRAINT and a CHECK condition.

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