GENERATED columns (stored and virtual) in PostgreSQL - Mini Project: Build & Apply

Create a PostgreSQL Table with GENERATED Columns (Stored)

📖 Scenario: You are managing a small online store database. You want to keep track of product prices and calculate the total price including tax automatically.

🎯 Goal: Create a PostgreSQL table called products with columns for product_name, price, and two stored generated columns: price_with_tax that adds 10% tax to the price, and price_with_discount that calculates a 5% discount on the price.

📋 What You'll Learn

Create a table named products

Add a product_name column of type TEXT

Add a price column of type NUMERIC(10,2)

Add a stored generated column price_with_tax that is price * 1.10

Add a stored generated column price_with_discount that is price * 0.95

💡 Why This Matters

🌍 Real World

Generated columns help automate calculations in databases, such as computing taxes, discounts, or derived values without manual updates.

💼 Career

Understanding generated columns is useful for database developers and administrators to optimize data integrity and reduce application logic.

1

Create the products table with basic columns

Write a SQL statement to create a table called products with two columns: product_name of type TEXT and price of type NUMERIC(10,2).

PostgreSQL

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

Need a hint?

Use CREATE TABLE products (product_name TEXT, price NUMERIC(10,2));

2

Add a stored generated column for price with tax

Alter the products table to add a stored generated column called price_with_tax that calculates price * 1.10.

PostgreSQL

CREATE TABLE products (
    product_name TEXT,
    price NUMERIC(10,2)
);
-- Add a stored generated column price_with_tax
-- Your code here

Need a hint?

Use price_with_tax NUMERIC(10,2) GENERATED ALWAYS AS (price * 1.10) STORED inside the table definition.

3

Add a stored generated column for price with discount

Modify the products table to add a stored generated column called price_with_discount that calculates price * 0.95. Use the GENERATED ALWAYS AS syntax with STORED.

PostgreSQL

CREATE TABLE products (
    product_name TEXT,
    price NUMERIC(10,2),
    price_with_tax NUMERIC(10,2) GENERATED ALWAYS AS (price * 1.10) STORED
);
-- Add a stored generated column price_with_discount
-- Your code here

Need a hint?

Use price_with_discount NUMERIC(10,2) GENERATED ALWAYS AS (price * 0.95) STORED in the table.

4

Complete the table definition with both stored generated columns

Ensure the products table has all four columns: product_name, price, the stored generated column price_with_tax, and the stored generated column price_with_discount. The full table definition should be a single CREATE TABLE statement.

PostgreSQL

CREATE TABLE products (
    product_name TEXT,
    price NUMERIC(10,2),
    price_with_tax NUMERIC(10,2) GENERATED ALWAYS AS (price * 1.10) STORED,
    price_with_discount NUMERIC(10,2) GENERATED ALWAYS AS (price * 0.95) STORED
);
-- Finalize the table definition
-- Your code here

Need a hint?

Make sure the full CREATE TABLE statement includes all columns with correct generated column syntax.