Generated Columns
Generated columns compute values from other columns in the row — stored or virtual — making derived fields first-class: indexable, selectable, and constraint-checkable without triggers.
Definition
A generated column is defined by an expression over sibling columns. Stored generated columns materialize the result in the row; virtual generated columns compute on read. The expression must be immutable. Generated columns can be indexed directly, participate in constraints, and remove trigger-based denormalization for stable derivations.
Context and scope
Used for normalized search keys, JSON field promotion, computed dimensions like month buckets, and unit conversions stored once.
Common misconceptions
- Generated columns are computed in application terms — the database computes them on write; the application cannot override them
- Virtual and stored behave the same for writes — virtual recompute on every read; stored pay space and write cost but read fast
- Any expression qualifies — immutability is required; now()- or locale-dependent expressions are rejected
Anti-patterns
- Duplicating the derivation in application code and generated columns, letting them drift apart
- Indexing virtual generated columns on hot read paths and paying recomputation per access in engines without storage
- Using generated columns for values that depend on other rows or tables, which the mechanism cannot express
Related concepts
Related examples
Authoritative source
https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html
What it is
Derived values — lowercased emails, extracted JSON fields, month rollups — either live in application discipline or in triggers. Generated columns move the derivation into the schema: the database computes it, keeps it consistent, and lets indexes and constraints treat it as a real column.
How it works
The DDL declares the expression and stored or virtual. Stored columns occupy row space and update when inputs change; virtual columns compute per read but can still be indexed by materializing entries in the index. PostgreSQL supports the stored variant; MySQL supports both. INSERT and UPDATE cannot assign generated columns.
When to use it
Stable row-local derivations: normalized codes, JSON path promotion with indexing, temporal bucket keys, checksums. When the derivation needs cross-row logic, a view or materialized view is the right level instead.
Pitfalls
Changing the expression is a schema change on real data, not an edit. Wide stored generated columns inflate rows and every secondary index built on them. Engines differ in allowed functions — porting DDL requires revalidating immutability and syntax per platform.