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

Need a 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

Need a 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

Need a 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

Need a hint?

Use ALTER TABLE with ADD CONSTRAINT and a CHECK condition.