Generated Column in PostgreSQL: Definition and Usage

A generated column in PostgreSQL is a special table column whose value is automatically computed from other columns in the same row using a defined expression. You cannot insert or update this column directly; PostgreSQL calculates its value whenever the row changes.

How It Works

Think of a generated column like a calculator built into your table. Instead of storing a value you type in, it automatically figures out its value based on other columns. For example, if you have columns for price and quantity, a generated column can automatically calculate the total_cost by multiplying them.

This means you don't have to manually update the calculated value every time the data changes. PostgreSQL does this for you behind the scenes, ensuring the generated column is always correct and up to date.

Example

This example shows how to create a table with a generated column that calculates the total price by multiplying unit_price and quantity.

sql

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  unit_price NUMERIC(10,2),
  quantity INT,
  total_price NUMERIC(10,2) GENERATED ALWAYS AS (unit_price * quantity) STORED
);

INSERT INTO orders (unit_price, quantity) VALUES (15.50, 3);
SELECT id, unit_price, quantity, total_price FROM orders;

Output

 id | unit_price | quantity | total_price 
----+------------+----------+-------------
  1 |      15.50 |        3 |       46.50
(1 row)

When to Use

Use generated columns when you want to keep calculated data consistent and avoid manual updates. They are great for storing values derived from other columns, like totals, concatenated strings, or dates calculated from other fields.

For example, in an e-commerce app, you can use generated columns to store the total cost of an order or a full name created by combining first and last names. This helps reduce errors and improves query performance since the value is stored, not computed on every query.

Key Points

• Generated columns are computed automatically from other columns using an expression.
• You cannot insert or update generated columns directly; PostgreSQL manages their values.
• They can be STORED, meaning the value is saved on disk for faster reads.
• Useful for keeping calculated data consistent and improving query speed.

Key Takeaways

Generated columns automatically compute their values from other columns using expressions.

You cannot manually change generated columns; PostgreSQL updates them when data changes.

They help keep calculated data consistent and improve query performance.

Use generated columns for totals, concatenations, or derived date values.

Generated columns are stored on disk when declared as STORED for efficient access.