Computed columns with generated fields in Supabase - Step-by-Step Execution

Process Flow - Computed columns with generated fields

Create Table with Columns

↓

Define Generated Column

↓

Insert Data into Table

↓

Database Automatically Computes Generated Column

↓

Query Table to See Computed Values

↓

Result

This flow shows how a table is created with a generated column that automatically computes its value from other columns when data is inserted or queried.

Execution Sample

Supabase

CREATE TABLE orders (
  price numeric,
  quantity integer,
  total numeric GENERATED ALWAYS AS (price * quantity) STORED
);

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

Creates a table with a generated column 'total' that multiplies price and quantity automatically when a row is inserted.

Process Table

Step	Action	Input Data	Generated Column Computation	Resulting Row
1	Create table 'orders' with generated column 'total'	N/A	N/A	Table schema ready
2	Insert row with price=10, quantity=3	price=10, quantity=3	total = 10 * 3	Row: {price:10, quantity:3, total:30}
3	Insert row with price=5, quantity=7	price=5, quantity=7	total = 5 * 7	Row: {price:5, quantity:7, total:35}
4	Query all rows	N/A	N/A	[{price:10, quantity:3, total:30}, {price:5, quantity:7, total:35}]
5	Attempt to insert row with NULL price	price=NULL, quantity=4	total = NULL * 4 = NULL	Row: {price:NULL, quantity:4, total:NULL}
6	Query all rows again	N/A	N/A	[{price:10, quantity:3, total:30}, {price:5, quantity:7, total:35}, {price:NULL, quantity:4, total:NULL}]
7	Exit	N/A	N/A	No more actions

💡 No more data inserted; all generated columns computed automatically based on input rows.

Status Tracker

Variable	Start	After Step 2	After Step 3	After Step 5	Final
price	N/A	10	5	NULL	NULL
quantity	N/A	3	7	4	4
total	N/A	30	35	NULL	NULL

Key Moments - 3 Insights

Why does the 'total' column automatically have a value after inserting price and quantity?

Because 'total' is a generated column defined as price * quantity, the database calculates it automatically when a row is inserted, as shown in execution_table rows 2 and 3.

What happens if one of the input columns used in the generated column is NULL?

The generated column result becomes NULL as well, since any arithmetic with NULL results in NULL, as seen in execution_table row 5.

Can you insert a value directly into the generated column 'total'?

No, generated columns are computed by the database and cannot be directly inserted or updated. The value is always derived from other columns.

Visual Quiz - 3 Questions

Look at the execution_table at step 3. What is the value of 'total' after inserting price=5 and quantity=7?

A 12

B 7

C 35

D NULL

💡 Hint

Check the 'Generated Column Computation' and 'Resulting Row' columns at step 3.

At which step does the 'total' column become NULL due to input data?

A Step 4

B Step 5

C Step 2

D Step 6

💡 Hint

Look for the step where price is NULL in the 'Input Data' column.

If you tried to insert a value directly into 'total', what would happen?

A The database would reject the insert or ignore the value

B The value would overwrite the computed value

C The value would be stored as is

D The total would be set to zero

💡 Hint

Recall that generated columns are computed automatically and cannot be manually set.

Concept Snapshot

CREATE TABLE with GENERATED ALWAYS AS defines a computed column.
The generated column calculates its value from other columns automatically.
You cannot insert or update generated columns directly.
If input columns are NULL, the generated column result is NULL.
Querying the table shows computed values without extra work.

Full Transcript

This visual execution shows how to create a table with a generated column in Supabase. The generated column 'total' is defined as price multiplied by quantity. When rows are inserted with price and quantity values, the database automatically computes the total. If any input is NULL, the total becomes NULL. You cannot insert values directly into the generated column. Querying the table returns rows with computed totals. This helps automate calculations inside the database.

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