2026-09-20 · 8 min read
- sql
- postgresql
- schema-design
A generated column moves a deterministic derivation into the table definition so every writer observes the same result. Typical uses include normalized search keys, extracted attributes, and arithmetic values that should remain synchronized with source columns.
Generated columns are not ordinary defaults and are not portable across database products without review. PostgreSQL version, generation kind, expression restrictions, type rules, privileges, inheritance, partitioning, and replication all affect a production design.
Choose a derivation suitable for schema
Use a generated column when the value is a row-local consequence of other columns and database-wide consistency is more valuable than application flexibility. Keep volatile context, cross-row lookups, external state, and frequently changing business policy out of the expression.
- Select an explicit result type and length.
- Choose collation for text comparison deliberately.
- Define null propagation and cast failures.
- Verify all functions satisfy server requirements.
Specify reads and writes clearly
Applications should omit the generated column from ordinary INSERT and UPDATE assignment lists, then read the computed value through SELECT or RETURNING when needed. Refresh ORM schemas and generated clients so they do not treat the field as writable.
Review column privileges separately from source-column privileges. A derived value can reveal information from its inputs, so exposing only the generated field still needs an authorization decision.
Index the expression only when justified
An index on the generated column can speed filtering and enforce constraints while keeping queries simple. Compare it with an expression index: both have storage and write costs, while a visible generated value may improve reuse and introspection.
Measure representative reads and writes. Large derived values, frequent source updates, and redundant indexes can cost more than computing a cheap expression at query time.
Plan migration and compatibility
Inventory existing rows, table size, locks, replication behavior, dump and restore support, and old application versions before rollout. Use phases when readers or writers cannot all update together, and verify the deployed expression from catalog metadata after migration.
Use Flashman's SQL formatter for schema and queries, JSON formatter for synthetic rows, diff for catalog definitions, hash for public deterministic examples, and case converter for normalization fixtures. Test nulls, Unicode, source updates, indexes, constraints, replication, rollback, and ORM behavior.