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

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

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

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

Hint

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

Practice

1. What is a GENERATED column in PostgreSQL?

easy

A. A column that stores user input without changes

B. A column that automatically calculates its value from other columns

C. A column that can only store text data

D. A column that requires manual updates every time

Solution

1. Step 1: Understand GENERATED column concept

   A GENERATED column automatically computes its value based on other columns in the same row.

2. Step 2: Compare options with definition

   Only A column that automatically calculates its value from other columns describes this automatic calculation feature correctly.

3. Final Answer:

   A column that automatically calculates its value from other columns -> Option B

4. Quick Check:

   GENERATED column = auto-calculated column [OK]

Hint: GENERATED means auto-calculated, not manual input [OK]

Common Mistakes:

• Thinking GENERATED columns store manual input
• Confusing GENERATED with regular stored columns
• Assuming GENERATED columns only store text

2. Which of the following is the correct syntax to create a STORED GENERATED column in PostgreSQL?

easy

A. CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a * 2) STORED);

B. CREATE TABLE t (a INT, b INT AS (a * 2));

C. CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a * 2) VIRTUAL);

D. CREATE TABLE t (a INT, b INT COMPUTED AS (a * 2));

Solution

1. Step 1: Recall PostgreSQL syntax for STORED GENERATED columns

   PostgreSQL uses 'GENERATED ALWAYS AS (expression) STORED' to define such columns.

2. Step 2: Check each option

   CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a * 2) STORED); matches the correct syntax. CREATE TABLE t (a INT, b INT AS (a * 2)); misses 'GENERATED ALWAYS' and 'STORED'. CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a * 2) VIRTUAL); uses 'VIRTUAL' which PostgreSQL does not support. CREATE TABLE t (a INT, b INT COMPUTED AS (a * 2)); uses 'COMPUTED' which is invalid in PostgreSQL.

3. Final Answer:

   CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a * 2) STORED); -> Option A

4. Quick Check:

   PostgreSQL STORED syntax = GENERATED ALWAYS AS (...) STORED [OK]

Hint: Use 'GENERATED ALWAYS AS (...) STORED' for PostgreSQL generated columns [OK]

Common Mistakes:

• Using VIRTUAL keyword which PostgreSQL does not support
• Omitting GENERATED ALWAYS clause
• Using COMPUTED instead of GENERATED

3. Given the table definition:

CREATE TABLE sales (
  price NUMERIC,
  quantity INT,
  total NUMERIC GENERATED ALWAYS AS (price * quantity) STORED
);

What will be the result of:

INSERT INTO sales (price, quantity) VALUES (10, 3);
SELECT total FROM sales;

medium

A. Syntax error

B. [13]

C. [NULL]

D. [30]

Solution

1. Step 1: Understand the GENERATED column calculation

   The 'total' column is calculated as price * quantity, so 10 * 3 = 30.

2. Step 2: Check the INSERT and SELECT results

   After inserting (10, 3), selecting 'total' returns 30 as the stored generated value.

3. Final Answer:

   [30] -> Option D

4. Quick Check:

   price * quantity = 10 * 3 = 30 [OK]

Hint: GENERATED columns auto-calculate on insert [OK]

Common Mistakes:

• Expecting manual input for generated column
• Confusing addition with multiplication
• Thinking generated columns can be NULL after insert

4. Consider this table creation:

CREATE TABLE products (
  price NUMERIC,
  discount NUMERIC,
  final_price NUMERIC GENERATED ALWAYS AS (price - discount)
);

What is wrong with this statement?

medium

A. final_price must be declared as VIRTUAL

B. Cannot use subtraction in GENERATED columns

C. Missing STORED keyword for the GENERATED column

D. price and discount must be integers

Solution

1. Step 1: Check PostgreSQL GENERATED column syntax

   PostgreSQL requires the STORED keyword for generated columns; omitting it causes an error.

2. Step 2: Analyze the given statement

   The statement misses 'STORED' after the expression, so it is invalid syntax.

3. Final Answer:

   Missing STORED keyword for the GENERATED column -> Option C

4. Quick Check:

   GENERATED columns must include STORED in PostgreSQL [OK]

Hint: Always add STORED after GENERATED expression in PostgreSQL [OK]

Common Mistakes:

• Assuming VIRTUAL columns are supported
• Thinking arithmetic is not allowed
• Believing data types must be integers

5. You want to create a table to store employee data with columns first_name, last_name, and a generated column full_name that combines both names separated by a space. Which is the correct way to define full_name in PostgreSQL?

hard

A. full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED

B. full_name TEXT GENERATED ALWAYS AS (first_name + ' ' + last_name) STORED

C. full_name TEXT AS (first_name || ' ' || last_name) VIRTUAL

D. full_name TEXT COMPUTED AS (first_name || ' ' || last_name)

Solution

1. Step 1: Use correct string concatenation operator in PostgreSQL

   PostgreSQL uses '||' to concatenate strings, not '+'.

2. Step 2: Use correct GENERATED column syntax

   PostgreSQL requires 'GENERATED ALWAYS AS (expression) STORED' for generated columns; VIRTUAL and COMPUTED are invalid.

3. Final Answer:

   full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED -> Option A

4. Quick Check:

   String concat with || and STORED keyword [OK]

Hint: Use || for string concat and STORED for generated columns [OK]

Common Mistakes:

• Using + instead of || for string concatenation
• Using VIRTUAL or COMPUTED keywords
• Omitting STORED keyword