Computed columns with generated fields in Supabase - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a generated column that concatenates first and last names.

Supabase

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

Drag options to blanks, or click blank then click option'

Alast_name

Bfirst_name

Cmiddle_name

Dusername

Attempts:

3 left

💡 Hint

Common Mistakes

Using first_name twice instead of last_name

Using a field that doesn't exist like middle_name

The generated column full_name combines first_name and last_name. So, last_name is the correct field to concatenate.

2fill in blank

medium

Complete the code to create a generated column that calculates the total price with tax.

Supabase

CREATE TABLE orders (id SERIAL PRIMARY KEY, price NUMERIC, tax_rate NUMERIC, total_price NUMERIC GENERATED ALWAYS AS (price * (1 + [1])) STORED);

Drag options to blanks, or click blank then click option'

Atotal_price

Bprice

Cdiscount

Dtax_rate

Attempts:

3 left

💡 Hint

Common Mistakes

Using price twice instead of tax_rate

Using discount which is not defined

The total_price is calculated by multiplying price by 1 + tax_rate. So, tax_rate is the correct field to use.

3fill in blank

hard

Fix the error in the generated column that calculates age from birth year.

Supabase

CREATE TABLE people (id SERIAL PRIMARY KEY, birth_year INT, age INT GENERATED ALWAYS AS ([1] - birth_year) STORED);

Drag options to blanks, or click blank then click option'

A2020

Bcurrent_year

CEXTRACT(YEAR FROM CURRENT_DATE)

Dbirth_year

Attempts:

3 left

💡 Hint

Common Mistakes

Using a fixed year like 2020 which becomes outdated

Using an undefined variable like current_year

To calculate age, we subtract birth year from the current year. Using EXTRACT(YEAR FROM CURRENT_DATE) gets the current year dynamically.

4fill in blank

hard

Fill both blanks to create a generated column that stores the domain from an email address.

Supabase

CREATE TABLE contacts (id SERIAL PRIMARY KEY, email TEXT, domain TEXT GENERATED ALWAYS AS (SUBSTRING(email FROM POSITION([1] IN email) + 1)) STORED);

Drag options to blanks, or click blank then click option'

A'@'

B' '

C'#'

D'_'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong characters like space or underscore

Not adding 1 to position to skip '@'

The domain part of an email starts after the '@' symbol. So, we find the position of '@' and extract the substring after it.

5fill in blank

hard

Fill all three blanks to create a generated column that calculates the full address by combining street, city, and zip code.

Supabase

CREATE TABLE addresses (id SERIAL PRIMARY KEY, street TEXT, city TEXT, zip TEXT, full_address TEXT GENERATED ALWAYS AS (street || [1] || city || [2] || [3]) STORED);

Drag options to blanks, or click blank then click option'

A', '

B' - '

Czip

Dcity

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong separators like ' - '

Forgetting to include the zip code field

Using city instead of zip in the last blank

The full address combines street, city, and zip code separated by commas and spaces. So, we use ', ' between street and city, ', ' between city and zip, and include the zip field.

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