Why Computed columns with generated fields in Supabase? - Purpose & Use Cases

The Big Idea

What if your database could do the math for you, perfectly every time?

The Scenario

Imagine you have a table of orders with separate columns for quantity and price. To get the total cost, you manually calculate it every time you query or update data.

This means you have to write the same calculation again and again in your app or reports.

The Problem

Manually calculating totals is slow and error-prone. You might forget to update the total when quantity or price changes.

This leads to inconsistent data and bugs that are hard to track down.

The Solution

Computed columns with generated fields automatically calculate values inside the database.

They keep totals always correct and up-to-date without extra code.

Before vs After

✗ Before

SELECT quantity, price, quantity * price AS total FROM orders;

✓ After

CREATE TABLE orders (quantity INT, price NUMERIC, total NUMERIC GENERATED ALWAYS AS (quantity * price) STORED);

What It Enables

This lets you trust your data is always accurate and reduces repetitive coding.

Real Life Example

In an online store, the total price for each order line is always correct without extra calculations in the app.

Key Takeaways

Manual calculations cause errors and extra work.

Generated fields automate value calculations inside the database.

This improves data accuracy and simplifies your code.

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