Postgres GENERATED ALWAYS AS Columns: Computed Values Without Application Code

AuthorVeraStreamEngineeringDepth2 — working knowledgeTypeDeep dive

A generated column computes its value from other columns in the same row. Postgres stores the result on every write. You declare it once in the schema; the engine enforces it everywhere.

The syntax

ALTER TABLE users
  ADD COLUMN full_name text
  GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

STORED is required in Postgres — the value is physically written to disk on every INSERT or UPDATE. There are no virtual generated columns in Postgres yet (MySQL has them; Postgres does not, as of PG16).

Practical use cases

Derived display fields: full_name from first_name || ' ' || last_name
Normalized search columns: email_lower text GENERATED ALWAYS AS (lower(email)) STORED — index this instead of indexing on lower(email) in an expression index
Hash columns for deduplication: md5(payload::text) as a quick fingerprint
Unit conversions: store weight in grams, generate a weight_kg column as weight_grams / 1000.0

Generated columns vs triggers

A trigger can do the same work, but generated columns are declarative — they appear in information_schema.columns with is_generated = 'ALWAYS', they are visible in \d tablename, and they are enforced by the storage engine rather than by application or migration code. You cannot forget to fire a trigger on one code path.

Generated columns vs application-level computation

Application-level computation has the same defect triggers have: you can forget to run it. A background worker that denormalizes data has a deployment window where the derived column is stale. A generated column is never stale — it is recomputed on every write by the engine itself.

The IMMUTABLE constraint

The expression must be IMMUTABLE — it cannot depend on mutable functions, session settings, or anything outside the current row. This means:

now() is forbidden — it is STABLE, not IMMUTABLE
lower(email) is allowed — deterministic regardless of locale when using the C collation, but watch out with locale-sensitive collations
timezone('UTC', created_at) is IMMUTABLE; created_at AT TIME ZONE current_setting('timezone') is not

Postgres will reject non-immutable expressions at DDL time. This is not a gotcha — it is the engine telling you that reproducible storage requires reproducible computation.

Limitations

Cannot reference other tables (no subqueries, no foreign-key lookups)
Cannot reference other generated columns in the same table
Cannot use non-immutable functions
No virtual/computed-only generated columns — STORED is the only option in Postgres
The column is read-only: INSERT INTO t (full_name) VALUES ('x') fails

Storage cost

STORED means the derived value occupies real disk space and incurs real write overhead. For a simple string concatenation this is negligible. For a large computed column (say, a 10KB serialized JSON blob derived from other columns), the cost compounds fast. Generated columns are the wrong tool for expensive computations — use a trigger or a materialized view there.

Migration behavior

Adding a generated column with ALTER TABLE ... ADD COLUMN triggers a table rewrite in Postgres versions before 11 (same as any column with a DEFAULT). In PG11+, columns with DEFAULT and no rewrite-required expression are metadata-only — but generated columns still rewrite the table because every existing row needs the expression evaluated. On large tables, do this in a maintenance window or use pg_repack to work around it.

When not to use generated columns

Volatile expressions that need updating on a schedule rather than on every write
Cross-table lookups — denormalize via a view or application logic instead
Expensive computations that should be lazy — compute on read, not on every write
When the derived value needs to be overridden by application logic — generated columns are not overridable

Building something? builds.anethoth.com is a public build ledger — proof that a product is really being built.