Computed columns with generated fields in Supabase - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a computed column in a database?

A computed column is a column that automatically calculates its value based on other columns in the same row.

beginner

How does a generated field differ from a regular column?

A generated field is a special column whose value is automatically computed by the database using an expression, and it updates automatically when dependent columns change.

intermediate

Why use computed columns with generated fields in Supabase?

They help keep data consistent and reduce manual updates by automatically calculating values like totals or concatenations within the database.

intermediate

What is the syntax to create a generated column in Supabase (PostgreSQL)?

Use: ALTER TABLE table_name ADD COLUMN column_name data_type GENERATED ALWAYS AS (expression) STORED;

beginner

Can you update a generated column directly in Supabase?

No, generated columns are read-only because their values are computed automatically from other columns.

What happens when you update a column that a generated field depends on?

A The database throws an error.

B The generated field stays the same until manually updated.

C The generated field deletes itself.

D The generated field updates automatically.

Generated fields recalculate their values automatically when dependent columns change.

Which keyword is used to define a generated column in Supabase's PostgreSQL?

A CALCULATED

B COMPUTED

C GENERATED ALWAYS AS

D AUTO_COLUMN

The syntax uses 'GENERATED ALWAYS AS (expression) STORED' to define generated columns.

Can you store a generated column value manually in Supabase?

A Yes, anytime.

B No, it's read-only.

C Only during insert.

D Only if you disable generation.

Generated columns are read-only and cannot be manually updated.

What is a benefit of using computed columns in your database?

A They reduce data duplication and errors.

B They increase manual work.

C They slow down queries.

D They require extra storage for manual updates.

Computed columns keep data consistent and reduce duplication by calculating values automatically.

Which of these is a valid use case for a generated column?

A Calculating total price from quantity and unit price.

B Storing user passwords.

C Saving user profile pictures.

D Logging user login times.

Generated columns are great for calculations like totals based on other columns.

Explain what a computed column with a generated field is and how it works in Supabase.

Think about how the database calculates values automatically without manual input.

Describe the benefits of using generated columns in a cloud database like Supabase.

Consider how automation helps keep data accurate and easier to manage.

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