Generated Columns in PostgreSQL

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.

Syntax

Declare a generated column in CREATE TABLE:

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

Add one 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 (no now(), random(), or subqueries).
• Cannot reference other generated columns.
• Cannot have a default value or an identity definition.
• Cannot be part of a partition key.

Practical Example

Create an order_lines table where the line total and a discounted price are computed automatically:

CREATE TABLE order_lines (
  line_id       SERIAL         PRIMARY KEY,
  order_id      INT            NOT NULL,
  product_name  VARCHAR(200)   NOT NULL,
  quantity      INT            NOT NULL,
  unit_price    NUMERIC(10, 2) NOT NULL,
  discount_pct  NUMERIC(5, 2)  NOT NULL DEFAULT 0,
  line_total    NUMERIC(12, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
  net_total     NUMERIC(12, 2) GENERATED ALWAYS AS (
                  quantity * unit_price * (1 - discount_pct / 100)
                ) STORED
);

Insert rows — line_total and net_total are computed automatically:

INSERT INTO order_lines (order_id, product_name, quantity, unit_price, discount_pct)
VALUES
  (101, 'Wireless Keyboard', 3, 49.99, 10.00),
  (101, 'USB Hub',           2, 29.99,  0.00)
RETURNING *;

 line_id | order_id | product_name      | qty | unit_price | discount_pct | line_total | net_total
---------+----------+-------------------+-----+------------+--------------+------------+-----------
       1 |      101 | Wireless Keyboard |   3 |      49.99 |        10.00 |     149.97 |    134.97
       2 |      101 | USB Hub           |   2 |      29.99 |         0.00 |      59.98 |     59.98

Verify the generated column definition using the system catalog:

SELECT attname, attgenerated
FROM pg_attribute
WHERE attrelid = 'order_lines'::regclass
  AND attgenerated = 's';

Add an index on a generated column for fast filtering:

CREATE INDEX idx_order_lines_net_total ON order_lines (net_total);

Trying to update a generated column raises an error:

UPDATE order_lines SET line_total = 0 WHERE line_id = 1;
-- ERROR: column "line_total" can only be updated to DEFAULT

Testing with Vela

Adding a generated column to a large table requires a full table rewrite. Before applying this on production, use Vela database branching to clone the database, run the ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS ... STORED statement, measure how long the rewrite takes, and validate the expression output against known values — all without touching the live system.

Production Tips

• Generated columns eliminate the need for triggers or application code to keep derived values in sync.
• Adding a generated column to a populated table triggers a full rewrite — plan it as a maintenance operation on large tables.
• You can index a generated column to speed up queries filtering or sorting on computed values.
• Only immutable expressions are permitted; for values dependent on the current time or session state, use a trigger instead.
• You cannot reference a generated column from another generated column’s expression in the same table.
• Generated column values are stored and counted toward row size limits, so avoid very wide expressions on wide tables.