GENERATED columns (stored and virtual) in PostgreSQL

Introduction

GENERATED columns automatically calculate their values based on other columns. This helps keep data consistent and saves time.

You want a column that always shows the total price by multiplying quantity and unit price.

You need a full name column that combines first and last names automatically.

You want to store a calculated age from a birthdate without manual updates.

You want to avoid mistakes by not letting users enter derived data manually.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  generated_column data_type GENERATED ALWAYS AS (expression) STORED
);

PostgreSQL supports only STORED generated columns, which save the calculated value.

The expression can use other columns but cannot call volatile functions.

Examples

This creates a total_cost column that multiplies price and quantity automatically.

PostgreSQL

CREATE TABLE products (
  price numeric,
  quantity int,
  total_cost numeric GENERATED ALWAYS AS (price * quantity) STORED
);

full_name combines first_name and last_name with a space.

PostgreSQL

CREATE TABLE users (
  first_name text,
  last_name text,
  full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

Sample Program

This example creates an orders table with a generated total_price column. It inserts two rows and selects all data to show the calculated total_price.

PostgreSQL

CREATE TABLE orders (
  unit_price numeric,
  quantity int,
  total_price numeric GENERATED ALWAYS AS (unit_price * quantity) STORED
);

INSERT INTO orders (unit_price, quantity) VALUES (10.5, 3);
INSERT INTO orders (unit_price, quantity) VALUES (7.25, 4);

SELECT * FROM orders;

Important Notes

PostgreSQL does not support virtual (non-stored) generated columns yet.

Generated columns are read-only; you cannot insert or update them directly.

Summary

GENERATED columns calculate values automatically from other columns.

PostgreSQL supports only STORED generated columns that save the result.

They help keep data consistent and reduce manual errors.