A table stores first_name and last_name, and half the application needs a full name. So a full_name column gets added, and application code sets it on insert. Then a second code path forgets, and a data migration script does not know about it, and someone updates a surname directly in a console during an incident.
Now full_name disagrees with its inputs on an unknown number of rows, and nothing will tell you which.
A generated column removes the possibility. The database computes it from an expression you define, on every write, from every source. There is no code path that can skip it.
What changed in 2026
- Support became broad and consistent. Generated columns moved from a database-specific feature to something available across mainstream engines with similar semantics.
- JSON extraction became the common use. Pulling a field out of a JSON column into an indexable generated column turned into a standard pattern for semi-structured data.
- Stored versus virtual diverged by engine. Which variants each database supports remained inconsistent, so portability requires checking.
- Search normalisation adopted them. Precomputing lowercase or accent-stripped forms for searching became a routine use.
Stored and virtual
Two variants, and which you have depends on your database.
Stored columns are computed on write and physically saved. They occupy disk space, they are read like any other column, and — critically — they can be indexed.
Virtual columns are computed on read and stored nowhere. No disk cost, computed every time, and generally not indexable.
|
Stored |
Virtual |
| Disk usage |
Yes |
None |
| Write cost |
Computed on every write |
None |
| Read cost |
None |
Computed per read |
| Indexable |
Yes |
Usually not |
| Availability |
Broad |
Varies by engine |
For most practical purposes, stored is what you want, and the reason is the index row. An indexable derived value is what makes generated columns worth adding rather than just using a view.
Where they earn their place
Indexing an expression. Searching case-insensitively means either an expression index or a generated column holding the lowercase form. The generated column is often clearer, and it can be referenced in queries directly rather than requiring the query to repeat the expression exactly for the planner to match it.
Extracting from JSON. A JSON column is flexible and awkward to index and filter well. A generated column pulling out the field you actually query gives you a normal, indexable, typed column while keeping the flexible document intact. This is the most common modern use.
Enforcing derived consistency. Anything computed from other columns — a total from a quantity and a price, a duration from two timestamps, a normalised identifier — stays correct by construction.
Search normalisation. Precomputing a stripped, lowercased, whitespace-collapsed form of a text field, then indexing that, makes fuzzy matching straightforward without repeating normalisation in every query.
The constraints
The expression must be deterministic. The same inputs must always produce the same output. That rules out the current time, random values, and anything depending on session settings. A column computing "days since created" cannot be generated, because its value changes without the row changing.
It can only reference the same row. No subqueries, no other tables, no aggregates. A generated column cannot hold a count of related rows — that is what a materialised view is for.
You cannot write to it. An insert or update naming a generated column is an error. This surprises ORMs that generate INSERT statements listing every column, and it is usually fixed with an annotation telling the ORM the column is read-only.
Changing the expression means rewriting the column. On a large table that is a migration with the same care as any other, and worth planning per expand and contract.
Common mistakes
- Trying to use non-deterministic expressions. Rejected, and the reason is not always obvious from the error.
- Expecting to reference other tables. Not possible; use a view or materialised view.
- ORM insert failures. Mark the column read-only in your model.
- Generating something never filtered or sorted on. A view costs nothing and stays flexible.
- Assuming virtual columns are indexable. Usually they are not, and this is engine-specific.
- Forgetting the write cost. A stored column is computed on every write; an expensive expression on a hot table is a real cost.
FAQ
Generated column or expression index?
Similar effect for indexing purposes. A generated column is more discoverable — it appears in the schema and can be selected and referenced by name — while an expression index is invisible unless you look for it and requires queries to match the expression exactly. Prefer the generated column when the value is meaningful to read.
Generated column or view?
A view is better when the value is only ever read and never filtered on, since it costs nothing and can be changed freely. A generated column is better when you need to index it.
Can I add one to a large existing table?
Adding a stored generated column requires computing it for every row, which rewrites the table and takes a lock in most engines. Treat it as a heavyweight migration on a large table.
Do they work with partitioning?
Generally yes, and support for using one as a partition key varies. Check your engine before designing around it — see table partitioning.
Where to go next
For indexing the values you generate, read database indexing explained and partial indexes. For derived values spanning multiple tables, what is a materialized view.