Generated columns (also called computed columns) let a database derive a column's value from other columns in the same row, instead of your application computing it and writing it back. They sound like a small convenience, but the way each SQL dialect implements them — what expressions are allowed, whether the value is stored on disk or computed on read, and whether you can index it — differs enough to break a migration if you assume one dialect's rules apply everywhere.
What Generated Columns Solve
Before generated columns, teams typically kept a derived value in sync with a trigger, an application-layer write, or a view. All three have downsides: triggers add hidden logic that's easy to forget during a migration, application-layer writes drift the moment someone updates a row directly, and views can't be indexed the same way a physical column can. A generated column keeps the derivation next to the schema definition itself, so it shows up in information_schema, in your ORM's introspection, and in any schema-diff tool — including FoxSchema's schema comparison view.
Stored vs Virtual: The Core Distinction
Every dialect that supports generated columns splits them into two flavors:
- Stored (persisted) — the value is computed on write and physically saved with the row. Reads are as fast as any other column, and you can index it without restriction, at the cost of extra storage and write overhead.
- Virtual — the value is computed on every read and never written to disk. It saves storage but limits indexing options in some dialects, and adds a small read-time cost.
Which flavor is the default, and whether the other is even available, is where the dialects diverge.
PostgreSQL, MySQL, and MariaDB
PostgreSQL only supports the stored variant: GENERATED ALWAYS AS (expression) STORED. There's no virtual option, and generated columns can't reference other generated columns or use subqueries — the expression must be immutable.
MySQL and MariaDB support both: GENERATED ALWAYS AS (expression) VIRTUAL or ... STORED, with virtual as the default if you omit the keyword. MariaDB additionally allows generated columns as part of a PRIMARY KEY if they're stored — MySQL does not. Both dialects let you index a virtual generated column directly, which PostgreSQL achieves differently since the value already lives on disk.
SQL Server, Oracle, and SQLite
SQL Server calls them computed columns and uses different syntax entirely: AS (expression) PERSISTED for stored, or just AS (expression) for virtual, which is the default. A persisted computed column can be indexed like any other; a non-persisted one can only be indexed if the expression is deterministic and precise.
Oracle uses virtual columns — GENERATED ALWAYS AS (expression) VIRTUAL — and, notably, does not support a stored variant at the column level at all; if you need the value physically materialized, you index the virtual column or use a materialized view instead.
SQLite follows the PostgreSQL-style syntax (GENERATED ALWAYS AS (expression) STORED or VIRTUAL, defaulting to virtual), but its expression rules are the strictest of the group: no subqueries, no non-deterministic functions, and no references to other tables.
Cross-Dialect Migration Gotchas
The failure mode we see most often is a straight copy-paste: a STORED clause that compiles fine on PostgreSQL fails on Oracle because Oracle has no stored keyword, or a MySQL virtual generated column used as a foreign key target fails outright because most dialects don't allow generated columns in foreign keys at all. Expression portability is the second trap — a function available in one dialect's generated-column expression (like PostgreSQL's immutable functions) may not be permitted in another's deterministic-function whitelist.
When you're moving a schema between dialects, treat every generated column as a manual-review item rather than a like-for-like copy: check whether the target supports stored vs. virtual, whether the expression's functions are allowed, and whether any index or constraint depends on it. FoxSchema's readiness panel flags generated columns for review during a cross-dialect migration rather than silently translating syntax that might not mean the same thing on the other side.
Ready to see how your own generated columns translate across dialects? Self-host FoxSchema or head to the install page to get started.
