Why GENERATED columns (stored and virtual) in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if your database could do math for you perfectly every time, without you lifting a finger?

The Scenario

Imagine you have a spreadsheet where you must calculate a total price by multiplying quantity and unit price for hundreds of rows. You do this manually each time you add or change data.

The Problem

Manually updating calculated values is slow and easy to forget. Mistakes happen, and your totals might be wrong. It's hard to keep data consistent and reliable.

The Solution

GENERATED columns automatically calculate values based on other columns. Stored columns save the result physically, while virtual columns compute on the fly. This keeps data accurate without extra work.

Before vs After

✗ Before

UPDATE orders SET total_price = quantity * unit_price;

✓ After

CREATE TABLE orders (quantity INT, unit_price NUMERIC, total_price NUMERIC GENERATED ALWAYS AS (quantity * unit_price) STORED);

What It Enables

You can trust your data is always correct and save time by letting the database handle calculations automatically.

Real Life Example

An online store uses GENERATED columns to keep track of order totals, taxes, and discounts without manual updates, ensuring accurate billing every time.

Key Takeaways

Manual calculations are error-prone and slow.

GENERATED columns automate and guarantee correct computed data.

Stored columns save results; virtual columns compute when needed.