Computed columns with generated fields in Supabase - Deep Dive

Overview - Computed columns with generated fields

What is it?

Computed columns with generated fields are special columns in a database table that automatically calculate their values based on other columns. Instead of storing data manually, these columns generate their content using formulas or expressions whenever you query the table. This helps keep data consistent and reduces errors by automating calculations inside the database.

Why it matters

Without computed columns, you would have to calculate values in your application code or update them manually, which can lead to mistakes and inconsistent data. Computed columns ensure that derived data is always accurate and up-to-date, saving time and reducing bugs. This makes your database smarter and your applications simpler.

Where it fits

Before learning computed columns, you should understand basic database tables and columns, and how data is stored. After this, you can explore triggers, views, and advanced database functions that build on automatic data handling. Computed columns are a step towards more automated and reliable database design.

Mental Model

Core Idea

A computed column is like a calculator inside your database that automatically fills in answers based on other data you already have.

Think of it like...

Imagine a grocery store price tag that shows the total cost for a product based on its weight and price per kilogram. Instead of writing the total price by hand, the tag updates itself whenever the weight or price changes.

┌───────────────┐
│   Table Row   │
├───────────────┤
│ Column A      │
│ Column B      │
│ ┌───────────┐ │
│ │Computed   │ │
│ │Column = A + B│
│ └───────────┘ │
└───────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding basic database columns

Concept: Learn what a database column is and how it stores data.

A database table is like a spreadsheet with rows and columns. Each column holds a specific type of data, like numbers or text. For example, a 'price' column stores numbers representing cost.

Result

You understand that columns hold data values for each row in a table.

Knowing what columns do is essential before adding any automatic calculations on top.

2

Foundation

What are generated (computed) columns?

Concept: Introduce columns that calculate their values automatically from other columns.

Generated columns do not store data you enter. Instead, they use a formula to calculate their value based on other columns in the same row. For example, a 'total' column might add 'price' and 'tax' automatically.

Result

You see that some columns can be automatic calculators inside the table.

This reduces manual work and keeps data consistent without extra code.

3

Intermediate

Creating generated columns in Supabase

🤔Before reading on: do you think generated columns store data physically or calculate on the fly? Commit to your answer.

Concept: Learn how to define generated columns using SQL in Supabase.

In Supabase, you create a generated column using SQL syntax like: ALTER TABLE products ADD COLUMN total_price numeric GENERATED ALWAYS AS (price + tax) STORED; This creates a 'total_price' column that always adds 'price' and 'tax'. The STORED keyword means the value is saved physically for faster reads.

Result

The database automatically fills 'total_price' whenever you insert or update 'price' or 'tax'.

Knowing the difference between STORED and VIRTUAL generated columns helps balance speed and storage.

4

Intermediate

Benefits of using generated columns

🤔Before reading on: do you think generated columns improve data consistency or just convenience? Commit to your answer.

Concept: Understand why generated columns improve database reliability and simplify applications.

Generated columns ensure calculations happen inside the database, so all applications see the same results. This avoids bugs from inconsistent calculations in different app parts. It also reduces the need for extra code to update derived data.

Result

Your data stays accurate and your app code is simpler.

Centralizing calculations in the database prevents common synchronization errors.

5

Advanced

Limitations and performance considerations

🤔Before reading on: do you think generated columns always improve performance? Commit to your answer.

Concept: Learn when generated columns might slow down writes or have restrictions.

Stored generated columns use extra disk space and slow down inserts/updates because the database must calculate and save the value. Virtual generated columns calculate on read, which can slow queries but save storage. Also, generated columns cannot reference other tables or non-deterministic functions.

Result

You understand trade-offs between storage, speed, and flexibility.

Balancing generated column types helps optimize your database for your workload.

6

Expert

Using generated columns in complex production schemas

🤔Before reading on: do you think generated columns can replace all application logic? Commit to your answer.

Concept: Explore advanced uses and limits of generated columns in real-world systems.

In production, generated columns are great for simple calculations and indexing. However, complex business logic often requires application code or triggers. Also, generated columns help with indexing computed values for faster searches. Knowing when to use them versus other database features is key.

Result

You can design schemas that leverage generated columns effectively without overusing them.

Understanding generated columns' scope prevents misuse and supports scalable, maintainable systems.

Under the Hood

Generated columns work by storing a formula or expression in the table schema. When a row is inserted or updated, the database engine evaluates this expression using the current row's data. For STORED columns, the result is saved physically; for VIRTUAL columns, the result is computed each time the column is queried. The database optimizer uses this information to maintain consistency and optimize queries.

Why designed this way?

Generated columns were designed to reduce duplication and errors by centralizing calculations in the database. Early databases required manual updates or triggers, which were error-prone and complex. Generated columns provide a declarative, simple way to keep derived data accurate and improve query performance by allowing indexing on computed values.

┌───────────────┐       ┌───────────────┐
│   Input Row   │──────▶│ Evaluate Expr │
│ price, tax   │       │ price + tax   │
└───────────────┘       └───────────────┘
          │                      │
          ▼                      ▼
┌───────────────────────────────┐
│ Store or Compute Generated Val│
│ (Stored: save result; Virtual:│
│ compute on query)             │
└───────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do generated columns always store data physically? Commit to yes or no.

Common Belief: Generated columns always store their computed values on disk.

Reality: Generated columns can be STORED (saved physically) or VIRTUAL (computed on read). Not all store data physically.

Why it matters: Assuming all generated columns store data can lead to unexpected performance issues or storage costs.

Quick: Can generated columns reference other tables? Commit to yes or no.

Common Belief: Generated columns can use data from other tables in their calculations.

Reality: Generated columns can only use data from the same row in the same table; they cannot reference other tables.

Why it matters: Trying to use other tables causes errors or forces complex workarounds like triggers.

Quick: Do generated columns replace all application logic? Commit to yes or no.

Common Belief: Generated columns can handle all business logic inside the database.

Reality: Generated columns are limited to simple deterministic calculations and cannot replace complex business rules or workflows.

Why it matters: Over-relying on generated columns can lead to inflexible designs and harder-to-maintain systems.

Quick: Are generated columns always faster than application-side calculations? Commit to yes or no.

Common Belief: Generated columns always improve performance compared to calculating in the application.

Reality: Generated columns can slow down writes due to extra computation and storage, so they are not always faster overall.

Why it matters: Misunderstanding this can cause unexpected slowdowns in high-write systems.

Expert Zone

1

Generated columns can be indexed to speed up queries on computed values, which is a powerful optimization often overlooked.

2

Choosing between STORED and VIRTUAL generated columns depends on workload patterns: STORED favors fast reads, VIRTUAL favors storage savings.

3

Some databases restrict functions allowed in generated columns to deterministic ones, which affects how you design expressions.

When NOT to use

Avoid generated columns when calculations require data from multiple tables or complex logic; use triggers, views, or application code instead. Also, if write performance is critical and calculations are expensive, consider computing values outside the database.

Production Patterns

In production, generated columns are commonly used for currency conversions, full name concatenations, or status flags derived from multiple fields. They are combined with indexes for fast filtering and sorting. Teams use them to enforce data consistency and reduce application complexity.

Connections

Database Views

Builds-on

Understanding generated columns helps grasp views, which also provide computed data but at query time and across multiple tables.

Functional Programming

Same pattern

Generated columns embody the idea of pure functions: output depends only on inputs, no side effects, which is a core functional programming concept.

Spreadsheet Formulas

Similar pattern

Like spreadsheet cells with formulas, generated columns automatically update based on other cells, showing how computation can be embedded in data structures.

Common Pitfalls

#1 Trying to reference other tables in a generated column expression.

Wrong approach:

ALTER TABLE orders ADD COLUMN total_amount numeric GENERATED ALWAYS AS (price + (SELECT tax FROM taxes WHERE id = orders.tax_id)) STORED;

Correct approach:

Use triggers or views to combine data from multiple tables instead of generated columns.

Root cause:

Misunderstanding that generated columns can only use data from the same row and table.

#2 Using non-deterministic functions in generated columns.

Wrong approach:

ALTER TABLE events ADD COLUMN event_time timestamp GENERATED ALWAYS AS (now()) STORED;

Correct approach:

Store timestamps explicitly or use triggers; generated columns must use deterministic expressions.

Root cause:

Not knowing generated columns require expressions that always produce the same output for the same input.

#3 Assuming generated columns improve write performance.

Wrong approach:

Adding many complex generated columns expecting faster inserts.

Correct approach:

Limit generated columns to simple calculations; optimize for read-heavy workloads.

Root cause:

Confusing read optimization with write performance impacts.

Key Takeaways

Computed columns automatically calculate values inside the database, reducing manual errors and simplifying applications.

Supabase supports generated columns that can be STORED or VIRTUAL, each with trade-offs in speed and storage.

Generated columns only use data from the same row and require deterministic expressions.

They improve data consistency and can be indexed for faster queries but may slow down writes.

Knowing when and how to use generated columns helps build efficient, reliable database schemas.

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