GENERATED columns (stored and virtual) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a stored generated column calculation

Consider the following table definition and insert statement in PostgreSQL:

CREATE TABLE sales (
  price numeric,
  quantity int,
  total numeric GENERATED ALWAYS AS (price * quantity) STORED
);

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

What will be the output of the query SELECT total FROM sales;?

PostgreSQL

CREATE TABLE sales (
  price numeric,
  quantity int,
  total numeric GENERATED ALWAYS AS (price * quantity) STORED
);

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

SELECT total FROM sales;

A 30

B NULL

C 13

D Syntax error

💡 Hint

Stored generated columns calculate and store the value automatically based on the expression.

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

📝 Syntax

intermediate

Correct syntax for generated column definition

Which of the following CREATE TABLE statements correctly defines a generated column in PostgreSQL?

A CREATE TABLE items (a int, b int GENERATED ALWAYS AS (a * 2) STORED);

B CREATE TABLE items (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);

C CREATE TABLE items (a int, b int AS (a * 2) VIRTUAL);

D CREATE TABLE items (a int, b int GENERATED ALWAYS AS (a * 2));

💡 Hint

PostgreSQL supports only STORED generated columns, not VIRTUAL.

PostgreSQL currently supports only STORED generated columns. The option with VIRTUAL keyword causes syntax error.

🧠 Conceptual

advanced

Difference between stored and virtual generated columns

Which statement correctly describes the difference between stored and virtual generated columns in databases?

A Stored columns compute values on query time; virtual columns store values physically.

B Stored columns store computed values physically; virtual columns compute values on query time without storing.

C Both stored and virtual columns store computed values physically but differ in update speed.

D Virtual columns are deprecated and replaced by stored columns in all databases.

💡 Hint

Think about when the value is calculated and if it is saved on disk.

Stored generated columns save the computed value physically in the table, while virtual columns compute the value on the fly when queried without storing it.

🔧 Debug

advanced

Why does this generated column cause an error?

Given the table definition:

CREATE TABLE orders (
  unit_price numeric,
  quantity int,
  total numeric NOT NULL GENERATED ALWAYS AS (unit_price * quantity) STORED
);

INSERT INTO orders (unit_price, quantity) VALUES (5, NULL);

Why does the insert statement fail?

PostgreSQL

CREATE TABLE orders (
  unit_price numeric,
  quantity int,
  total numeric NOT NULL GENERATED ALWAYS AS (unit_price * quantity) STORED
);

INSERT INTO orders (unit_price, quantity) VALUES (5, NULL);

A Because generated columns cannot reference nullable columns.

B Because the table definition is missing a primary key.

C Because the multiplication operator is invalid for numeric and NULL types.

D Because quantity is NULL, the generated column expression results in NULL which is not allowed for stored columns.

💡 Hint

Consider how NULL values affect expressions in generated columns.

The expression unit_price * quantity evaluates to NULL when quantity is NULL. Stored generated columns cannot store NULL if the column is defined NOT NULL or if the database disallows it.

❓ optimization

expert

Optimizing queries using stored generated columns

You have a table with a stored generated column that calculates total_price as unit_price * quantity. Which query will generally perform better in PostgreSQL?

A SELECT unit_price, quantity FROM sales WHERE unit_price * quantity > 100;

B SELECT unit_price * quantity AS total_price FROM sales WHERE unit_price * quantity > 100;

C SELECT total_price FROM sales WHERE total_price > 100;

D SELECT total_price FROM sales WHERE unit_price * quantity > 100;

💡 Hint

Think about how stored generated columns can be indexed and reused.

Using the stored generated column in the WHERE clause allows PostgreSQL to use indexes on that column, improving performance. Calculating the expression repeatedly in the query is less efficient.

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