Generated Columns | PostgreSQL Tutorial

A generated column in PostgreSQL is a column whose value is automatically computed from an expression based on other columns in the same row. This keeps derived values consistent without requiring application logic or triggers. PostgreSQL currently supports only stored generated columns — the computed value is written to disk on each insert or update, analogous to a materialized view at the row level.

Generated column syntax

CREATE TABLE table_name (
  ...
  column_name type GENERATED ALWAYS AS (expression) STORED,
  ...
);

To add a generated column to an existing table:

ALTER TABLE table_name
  ADD COLUMN column_name type GENERATED ALWAYS AS (expression) STORED;

Expression requirements:

Must use only immutable functions (e.g., no now() or random()).
Cannot reference other generated columns or subqueries.
Cannot have a default value or an identity definition.
Cannot be part of a partition key.

Generated column examples

Example 1 — full name concatenated from first and last name:

CREATE TABLE contacts (
  id         SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name  VARCHAR(50) NOT NULL,
  full_name  VARCHAR(101) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
  email      VARCHAR(300) UNIQUE
);

INSERT INTO contacts (first_name, last_name, email)
VALUES
  ('John', 'Doe', '[email protected]'),
  ('Jane', 'Doe', '[email protected]')
RETURNING *;

 id | first_name | last_name | full_name |              email
----+------------+-----------+-----------+---------------------------------
  1 | John       | Doe       | John Doe  | [email protected]
  2 | Jane       | Doe       | Jane Doe  | [email protected]
(2 rows)

Example 2 — net price computed from list price, tax, and discount:

CREATE TABLE products (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  list_price DECIMAL(10, 2) NOT NULL,
  tax        DECIMAL(5, 2) DEFAULT 0,
  discount   DECIMAL(5, 2) DEFAULT 0,
  net_price  DECIMAL(10, 2) GENERATED ALWAYS AS (
               (list_price + (list_price * tax / 100))
               - (list_price * discount / 100)
             ) STORED
);

INSERT INTO products (name, list_price, tax, discount)
VALUES ('A', 100.00, 10.00, 5.00), ('B', 50.00, 8.00, 0.00)
RETURNING *;

 id | name | list_price |  tax  | discount | net_price
----+------+------------+-------+----------+-----------
  1 | A    |     100.00 | 10.00 |     5.00 |    105.00
  2 | B    |      50.00 |  8.00 |     0.00 |     54.00
(2 rows)

Generated column tips

Generated columns eliminate redundant logic in application code and keep derived values in sync automatically on every write.
You can create indexes on generated columns to speed up queries that filter or sort by the computed value.
Generated columns cannot be written to directly in INSERT or UPDATE statements — omit them from the column list.
Because only immutable expressions are allowed, complex calculations involving the current time or external state require triggers instead of generated columns.

Reference: PostgreSQL documentation — Generated Columns.

Frequently Asked Questions

What is a generated column in PostgreSQL?

A generated column is a column whose value is automatically computed from an expression using other columns in the same row. It is declared with GENERATED ALWAYS AS (expression) STORED and is updated automatically on every insert or update. You cannot write a value directly to a generated column.

What is the difference between a stored and a virtual generated column?

A stored generated column is physically written to disk on each insert/update. A virtual generated column is computed on-the-fly when read and uses no storage space. PostgreSQL currently supports only stored generated columns.

Can I create an index on a generated column?

Yes. Because generated column values are physically stored, you can create a regular B-tree or other index on them just like any other column. This is useful for full-name search or computed price filtering.

What functions can I use in a generated column expression?

Only immutable functions — functions that always return the same result for the same inputs, such as arithmetic operators, string concatenation (||), LOWER(), UPPER(), and mathematical functions. Functions like now(), random(), and nextval() are not immutable and cannot be used.

Can I add a generated column to an existing table?

Yes. Use ALTER TABLE table_name ADD COLUMN col_name type GENERATED ALWAYS AS (expression) STORED. PostgreSQL will compute and store the value for all existing rows during the ALTER TABLE operation.