Generated Columns

Automatically compute column values from other columns using GENERATED ALWAYS AS.

Overview

Generated columns (PostgreSQL 12+) are computed automatically from expressions involving other columns in the same row. STORED means the value is physically stored and recomputed on INSERT/UPDATE. Generated columns cannot be manually set they are always derived from the expression. Common uses: full_name = first_name || ' ' || last_name, total = quantity * unit_price, search_vector = to_tsvector(body).

Key Points

•col TYPE GENERATED ALWAYS AS (expr) STORED
•Value computed automatically from other columns
•Cannot manually INSERT or UPDATE a generated column
•Can be indexed like a regular column
•Only STORED generated columns (computed and saved) are supported in PostgreSQL
•Good for search vectors, computed totals, full names

Examples

Generated columns for computed values

CREATE TABLE order_items (
  id          SERIAL PRIMARY KEY,
  quantity    INTEGER NOT NULL,
  unit_price  NUMERIC(10,2) NOT NULL,
  line_total  NUMERIC(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,
  full_name  TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
  search_vec TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', first_name || ' ' || last_name)) STORED
);

Generated Columns

Overview

Key Points

Examples

Exercises

Knowledge Check

Can you manually INSERT a value into a generated column?