GENERATED columns (stored and virtual) in PostgreSQL - Step-by-Step Execution

Concept Flow - GENERATED columns (stored and virtual)

Create Table

↓

Define Columns

↓

Define Regular Columns

↓

Define GENERATED Column

↓

Specify Expression

↓

Choose STORED or VIRTUAL

↓

Insert Data

↓

GENERATED Column Value Computed

↓

Query Table

↓

See Computed Values

When creating a table, you define normal columns and GENERATED columns with expressions. The GENERATED column computes its value automatically, either stored physically or computed on the fly.

Execution Sample

PostgreSQL

CREATE TABLE products (
  price numeric,
  quantity int,
  total_cost numeric GENERATED ALWAYS AS (price * quantity) STORED
);

Creates a table with a stored generated column that calculates total_cost as price times quantity.

Execution Table

Step	Action	Input Data	Generated Column Computation	Resulting Row
1	Insert row	price=10, quantity=3	total_cost = 10 * 3 = 30	(10, 3, 30)
2	Insert row	price=5, quantity=7	total_cost = 5 * 7 = 35	(5, 7, 35)
3	Query table	N/A	Values retrieved from stored column	(10, 3, 30), (5, 7, 35)
4	Update price	price=6 for first row	total_cost recalculated = 6 * 3 = 18	(6, 3, 18)
5	Query table	N/A	Updated stored values shown	(6, 3, 18), (5, 7, 35)
6	Insert row with NULL	price=NULL, quantity=4	total_cost = NULL * 4 = NULL	(NULL, 4, NULL)
7	Query table	N/A	NULL shown for total_cost	(6, 3, 18), (5, 7, 35), (NULL, 4, NULL)
8	Attempt to insert invalid data	price='abc', quantity=2	Error: invalid input for numeric	Insert fails

💡 Execution stops after error on invalid data insert.

Variable Tracker

Variable	Start	After 1	After 2	After 4	After 6	Final
price	N/A	10	5	6	NULL	6
quantity	N/A	3	7	3	4	3
total_cost	N/A	30	35	18	NULL	18

Key Moments - 3 Insights

Why does total_cost update automatically when price changes?

Because total_cost is a GENERATED column defined as price * quantity, it recalculates whenever price or quantity changes, as shown in execution_table row 4.

What happens if one of the input columns is NULL?

The generated column result becomes NULL because any arithmetic with NULL yields NULL, as seen in execution_table row 6.

Why does inserting a non-numeric value for price cause an error?

Because price is numeric type, inserting 'abc' is invalid and the database rejects it, shown in execution_table row 8.

Visual Quiz - 3 Questions

Look at the execution_table, what is total_cost after the first insert?

A 13

B 30

C 3

D NULL

💡 Hint

Check execution_table row 1 under Generated Column Computation.

At which step does total_cost become NULL?

A Step 6

B Step 2

C Step 4

D Step 8

💡 Hint

Look at execution_table row 6 where price is NULL.

If total_cost was defined as VIRTUAL instead of STORED, what would change?

A Values would be stored physically in the table.

B You could insert values manually into total_cost.

C Values would be computed on query, not stored.

D The table would reject NULL values.

💡 Hint

Recall the difference between STORED and VIRTUAL generated columns.

Concept Snapshot

GENERATED columns compute values automatically from other columns.
Syntax: column_name data_type GENERATED ALWAYS AS (expression) STORED|VIRTUAL.
STORED saves computed values physically; VIRTUAL computes on query.
Useful for derived data like totals or concatenations.
Cannot insert or update generated columns directly.

Full Transcript

This lesson shows how GENERATED columns work in MySQL. When creating a table, you can define columns that automatically calculate their values from other columns using an expression. These columns can be STORED, meaning their values are saved in the table, or VIRTUAL, meaning their values are calculated when you query the table. For example, a total_cost column can be generated as price times quantity. When you insert or update rows, the database automatically calculates total_cost. If input columns are NULL, the generated column becomes NULL. Invalid data types cause errors. This visual trace shows step-by-step how data is inserted, how generated columns compute values, and how updates affect them.

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