A generated column is computed from other columns by an expression the database maintains. It removes the need to keep a derived value in sync in application code, so a full_name or email_domain column cannot drift from its inputs.
Before you start
You should be comfortable with ALTER TABLE and expressions. This article uses PostgreSQL, which supports STORED generated columns.
Step-by-step walkthrough
Step 1: Define the expression once
GENERATED ALWAYS AS (expr) tells the database to compute the value. The expression must be immutable for the row, so it cannot depend on volatile functions such as now() or on other rows. The column is read-only: you cannot insert or update it directly.
Step 2: Choose STORED when you need to index
A STORED generated column is computed on write and saved, so it can be indexed and read cheaply. PostgreSQL currently supports only STORED, while some engines offer virtual columns computed on read. Stored trades write cost and space for query speed.
Step 3: Keep the expression deterministic
Because the value is maintained by the database, the expression is the single source of truth. If the expression is not immutable, the stored value could be inconsistent with a re-evaluation, and the database rejects functions that violate the rule. Keep it a pure function of the row’s other columns.
Worked scenario
The domain column is derived from the email and kept in sync automatically.
ALTER TABLE users
ADD COLUMN email_domain text
GENERATED ALWAYS AS (split_part(email, '@', 2)) STORED;
SELECT email_domain, count(*)
FROM users
GROUP BY email_domain;Walk through the example
Every insert or update of email recomputes email_domain, so the grouping reflects the current data without a trigger or application code. Because it is STORED, the value can back an index for fast grouping. Attempting to write the column directly is rejected, which guarantees the derivation holds.
Common mistake
Trying to insert or update a generated column, which errors. Another is using a volatile expression such as a timestamp, which is not allowed for a stored column and, if it were, would not be reproducible.
Verify the behavior
Insert a row and confirm the generated value matches the expression. Update the source column and confirm the generated column changes. Attempt a direct write and confirm the error, then add an index on the stored column and check the plan uses it.
Interview exercise
Why can a generated column not depend on the current time?
Answer and reasoning
The database guarantees that the stored value equals the expression applied to the row. A time-dependent expression would produce a different value whenever it was evaluated, so the stored value could not be reproduced and the guarantee would fail. Generated expressions must be immutable functions of the row’s own columns.
Continue learning
Compare invariants in SQL constraints as invariants and normalization trade-offs. Read the PostgreSQL generated columns documentation and try the SQL interview questions.