Computed columns with generated fields in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Computed Columns in Supabase

In Supabase, you want to create a computed column that automatically shows the full name by combining first and last names stored in a table. Which SQL statement correctly creates a generated column for this purpose?

Supabase

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

A CREATE TABLE users (id SERIAL PRIMARY KEY, first_name TEXT, last_name TEXT, full_name TEXT AS (first_name + ' ' + last_name));

B CREATE TABLE users (id SERIAL PRIMARY KEY, first_name TEXT, last_name TEXT, full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL);

C CREATE TABLE users (id SERIAL PRIMARY KEY, first_name TEXT, last_name TEXT, full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name));

D CREATE TABLE users (id SERIAL PRIMARY KEY, first_name TEXT, last_name TEXT, full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED);

💡 Hint

Look for the correct syntax for generated columns in PostgreSQL, which Supabase uses.

Option D correctly uses the GENERATED ALWAYS AS syntax with STORED keyword and concatenates strings with ||. Option D uses invalid concatenation with +. Option D misses the STORED keyword which is required. Option D uses VIRTUAL which PostgreSQL does not support.

❓ Configuration

intermediate

Adding a Computed Column to an Existing Table

You have an existing table orders with columns price and quantity. You want to add a computed column total_cost that multiplies these two. Which ALTER TABLE statement correctly adds this generated column?

Supabase

ALTER TABLE orders ADD COLUMN total_cost numeric GENERATED ALWAYS AS (price * quantity) STORED;

A ALTER TABLE orders ADD COLUMN total_cost numeric GENERATED ALWAYS AS (price * quantity) STORED;

B ALTER TABLE orders ADD COLUMN total_cost numeric AS (price * quantity);

C ALTER TABLE orders ADD COLUMN total_cost numeric GENERATED ALWAYS AS (price * quantity);

D ALTER TABLE orders ADD COLUMN total_cost numeric VIRTUAL AS (price * quantity);

💡 Hint

Remember that PostgreSQL requires the STORED keyword for generated columns.

Option A correctly adds a stored generated column. Option A misses the GENERATED ALWAYS and STORED keywords. Option A misses STORED. Option A uses VIRTUAL which is not supported in PostgreSQL.

❓ Architecture

advanced

Choosing Between Computed Columns and Views

You want to optimize read performance for a Supabase app that frequently queries user full names. You can either use a computed generated column or create a view that concatenates first and last names. Which approach is better for performance and why?

A Use a view because it caches the computed values automatically for faster reads.

B Use a computed generated column because it stores the computed value physically, improving read speed.

C Use a view because it always shows the latest data without storing extra data.

D Use a computed generated column because views cannot concatenate strings.

💡 Hint

Think about whether the computed value is stored or calculated on the fly.

Computed generated columns store the result physically, so reads are faster. Views compute values on the fly each time, which can be slower. Option B is incorrect because views do not cache results automatically. Option B is false because views can concatenate strings.

❓ security

advanced

Security Implications of Computed Columns

In Supabase, you have a computed column that concatenates sensitive user data. What is a key security consideration when using computed generated columns?

A Computed columns inherit the same access controls as the base columns, so sensitive data exposure depends on table permissions.

B Computed columns automatically encrypt sensitive data to protect it.

C Computed columns are not stored, so they cannot leak sensitive data.

D Computed columns require separate permission settings from the base table.

💡 Hint

Consider how database permissions apply to generated columns.

Computed columns are part of the table and follow the same access controls. They do not encrypt data automatically (B is false). They are stored physically if STORED, so (C) is false. Permissions are managed at the table level, so (D) is false.

❓ service_behavior

expert

Behavior of Computed Columns on Data Update

In Supabase, you have a table with a computed generated column that concatenates two fields. What happens to the computed column when you update one of the base fields?

A The computed column must be dropped and recreated to update.

B The computed column remains unchanged until the table is manually refreshed.

C The computed column automatically updates to reflect the new values immediately.

D The computed column updates only after a database restart.

💡 Hint

Think about how generated columns behave on data changes.

Generated columns are automatically recalculated on insert or update, so they always reflect current data. Options A, B, and C are incorrect because no manual action is needed to update the computed column.

Practice

1. What is the main purpose of a computed column with a generated field in Supabase?

easy

A. To manually enter data that updates other columns

B. To automatically calculate and store values based on other columns

C. To create a backup of existing columns

D. To delete rows based on a condition

Solution

1. Step 1: Understand computed columns

   Computed columns automatically calculate their values using other columns, reducing manual work.

2. Step 2: Recognize generated fields usage

   Generated fields use the syntax GENERATED ALWAYS AS (expression) STORED to keep data consistent.

3. Final Answer:

   To automatically calculate and store values based on other columns -> Option B

4. Quick Check:

   Computed columns = automatic calculation [OK]

Hint: Computed columns calculate values automatically [OK]

Common Mistakes:

• Thinking computed columns require manual input
• Confusing computed columns with backups
• Assuming computed columns delete data

2. Which of the following is the correct syntax to create a computed column named full_name that concatenates first_name and last_name in Supabase?

easy

A. ALTER TABLE users ADD COLUMN full_name TEXT GENERATED BY DEFAULT AS (first_name || last_name);

B. ALTER TABLE users ADD full_name TEXT AS (first_name + last_name);

C. CREATE COLUMN full_name AS CONCAT(first_name, last_name);

D. ALTER TABLE users ADD COLUMN full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

Solution

1. Step 1: Identify correct syntax for generated columns

   Supabase uses PostgreSQL syntax: GENERATED ALWAYS AS (expression) STORED.

2. Step 2: Check concatenation and syntax correctness

   ALTER TABLE users ADD COLUMN full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED; correctly uses || for string concatenation and the full syntax.

3. Final Answer:

   ALTER TABLE users ADD COLUMN full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED; -> Option D

4. Quick Check:

   Correct syntax = ALTER TABLE users ADD COLUMN full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED; [OK]

Hint: Use GENERATED ALWAYS AS (expression) STORED for computed columns [OK]

Common Mistakes:

• Using + instead of || for string concatenation
• Omitting STORED keyword
• Using GENERATED BY DEFAULT instead of ALWAYS

3. Given the table orders with columns quantity (integer) and price_per_unit (numeric), what will be the value of the computed column total_price defined as GENERATED ALWAYS AS (quantity * price_per_unit) STORED for a row where quantity = 3 and price_per_unit = 10.5?

medium

A. 3

B. 315

C. 31.5

D. 13.5

Solution

1. Step 1: Understand the computed column expression

   The total_price is calculated by multiplying quantity by price_per_unit.

2. Step 2: Calculate the value for given inputs

   3 * 10.5 = 31.5

3. Final Answer:

   31.5 -> Option C

4. Quick Check:

   3 x 10.5 = 31.5 [OK]

Hint: Multiply quantity and price_per_unit for total_price [OK]

Common Mistakes:

• Multiplying incorrectly (e.g., 3 * 105)
• Confusing addition with multiplication
• Ignoring decimal in price_per_unit

4. You tried to create a computed column with this command:
ALTER TABLE products ADD COLUMN discount_price NUMERIC GENERATED ALWAYS AS (price - discount) STORED;
But you get an error. What is the most likely cause?

medium

A. One or both columns price or discount do not exist

B. The syntax for GENERATED ALWAYS AS is incorrect

C. Computed columns cannot use arithmetic operations

D. The column discount_price already exists

Solution

1. Step 1: Check syntax correctness

   The syntax is correct for a computed column using arithmetic.

2. Step 2: Identify common error causes

   An error often occurs if referenced columns like price or discount do not exist in the table.

3. Final Answer:

   One or both columns price or discount do not exist -> Option A

4. Quick Check:

   Missing columns cause errors [OK]

Hint: Verify referenced columns exist before adding computed column [OK]

Common Mistakes:

• Assuming syntax is wrong without checking columns
• Thinking arithmetic is not allowed in computed columns
• Not checking if column already exists

5. You want to create a computed column age_group in a users table that categorizes users as 'minor' if age < 18, 'adult' if age is between 18 and 64, and 'senior' if 65 or older. Which of the following is the correct way to define this computed column in Supabase?

hard

A. ALTER TABLE users ADD COLUMN age_group TEXT GENERATED ALWAYS AS (CASE WHEN age < 18 THEN 'minor' WHEN age BETWEEN 18 AND 64 THEN 'adult' ELSE 'senior' END) STORED;

B. ALTER TABLE users ADD COLUMN age_group TEXT GENERATED ALWAYS AS (IF age < 18 THEN 'minor' ELSE IF age < 65 THEN 'adult' ELSE 'senior' END) STORED;

C. ALTER TABLE users ADD COLUMN age_group TEXT GENERATED ALWAYS AS (age < 18 ? 'minor' : age < 65 ? 'adult' : 'senior') STORED;

D. ALTER TABLE users ADD COLUMN age_group TEXT GENERATED ALWAYS AS (SWITCH(age) { CASE < 18: 'minor'; CASE < 65: 'adult'; DEFAULT: 'senior'; }) STORED;

Solution

1. Step 1: Identify correct conditional syntax in PostgreSQL

   PostgreSQL uses CASE WHEN ... THEN ... ELSE ... END for conditional expressions.

2. Step 2: Verify the CASE expression matches the age groups

   ALTER TABLE users ADD COLUMN age_group TEXT GENERATED ALWAYS AS (CASE WHEN age < 18 THEN 'minor' WHEN age BETWEEN 18 AND 64 THEN 'adult' ELSE 'senior' END) STORED; correctly uses CASE with conditions for minor, adult, and senior.

3. Step 3: Confirm full syntax for computed column

   ALTER TABLE users ADD COLUMN age_group TEXT GENERATED ALWAYS AS (CASE WHEN age < 18 THEN 'minor' WHEN age BETWEEN 18 AND 64 THEN 'adult' ELSE 'senior' END) STORED; uses GENERATED ALWAYS AS (...) STORED correctly.

4. Final Answer:

   ALTER TABLE users ADD COLUMN age_group TEXT GENERATED ALWAYS AS (CASE WHEN age < 18 THEN 'minor' WHEN age BETWEEN 18 AND 64 THEN 'adult' ELSE 'senior' END) STORED; -> Option A

5. Quick Check:

   Use CASE WHEN for conditions in computed columns [OK]

Hint: Use CASE WHEN for multi-condition computed columns [OK]

Common Mistakes:

• Using IF or SWITCH which are not valid in PostgreSQL expressions
• Incorrect ternary syntax in SQL
• Missing END in CASE expression