Why do relational engines require the expression behind a generated column to be deterministic (immutable), and what kinds of expressions get rejected as a result?

## What determinism means here An expression is deterministic (PostgreSQL's term is IMMUTABLE) when it returns the same output for the same inputs, forever, on any node, with any session settings. `unit_price * qty` qualifies. `now()` does not, because the input clock is invisible in the row. `random()` does not. `lower(email)` qualifies under a fixed collation but becomes questionable if the transformation depends on a locale the engine may not treat as fixed. ## Why the engine insists on it The value of a generated column is not computed once and forgotten. It gets recomputed, or the previously computed bytes get trusted, in several places: 1. **Write path** - evaluated at insert/update and, for STORED columns, written into the row. 2. **Index maintenance** - if the column is indexed, the same value is copied into the index entry. An index build later recomputes it for existing rows. 3. **Table rewrites** - full-table maintenance operations, ALTER TABLE rewrites, and online schema-change tools recompute or copy the value. 4. **Backup and restore** - a logical dump reloads rows and the target recomputes generated values. 5. **Replication and failover** - the replica must arrive at the same value as the primary, whether it receives physical bytes or logical row images. If the expression drifted, each of these would produce a different answer. The disaster case is quiet: an index entry says the value is one thing while the row now recomputes to another. Index-only scans return the stale value, index scans miss rows that a sequential scan would find, and the same query returns different results depending on the plan the optimizer chose. That is corruption, not a bug you can log. A UNIQUE constraint on such a column would be enforcing a rule that no longer holds. Determinism is what lets the engine treat a computed value as data. ## The rejected categories, with the reasoning - **Clock functions.** `now()`, `current_timestamp`, `localtime`. The tempting use case - an auto-filled `created_at` - is exactly the one that breaks: the value must be captured once, not re-derived. Use a column DEFAULT, which fires once at insert and then is ordinary data. - **Session and environment.** `current_user`, configuration lookups, session time zone. Two sessions would compute different values for the same row. - **Random and sequence-like sources.** `random()`, UUID generators, sequence access. These have side effects or no repeatability at all. - **Anything cross-row.** Subqueries, joins, aggregates, and window functions are all refused because a generated column is evaluated with only the current row in scope, and a change to some other row could not trigger re-evaluation. - **User-defined functions with the wrong volatility.** PostgreSQL requires IMMUTABLE. A function marked STABLE - constant within one statement but allowed to change between statements, typically because it reads tables or settings - is not enough for something written to disk. Developers routinely mislabel a function IMMUTABLE to get past the error; that does not make it immutable, it just moves the failure to the next restore or index build. - **Text conversions that depend on settings.** Formatting a timestamp-with-time-zone as text uses the session time zone; casting under a collation that could be upgraded is similarly fragile. Some engines reject these outright; where they do not, the risk is real - a collation library upgrade can change ordering and case rules, which is why collation changes force index rebuilds. ## Related restrictions worth naming Beyond determinism, engines commonly forbid a generated column from being part of a primary key in some products, restrict whether one generated column may reference another (PostgreSQL forbids it; MySQL allows a reference to one defined earlier), and forbid direct writes entirely. Foreign keys referencing or originating from generated columns are allowed in some engines and not others, so check before designing around it. ## What to do instead When you need a non-deterministic derived value, name the right tool. Insert-time capture of the clock: a DEFAULT. Update-time capture: a BEFORE UPDATE trigger, or set it in the application's write path. Values derived from other rows or tables: a view, a materialized view, or explicit maintenance logic. Trying to force any of these into a generated column is the single most common mistake on this topic, and interviewers ask precisely because it separates people who have used the feature from people who have read the syntax.