Recommended Free Tools
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Use a PostgreSQL generated column when a value is a deterministic calculation from columns in the same row and its expression satisfies PostgreSQL’s restrictions. Use a trigger when the rule needs procedural logic, other data, or custom event handling. The choice also depends on your PostgreSQL version: PostgreSQL 17 supports stored generated columns, while PostgreSQL 18 adds virtual generated columns.
How to choose between a generated column and a trigger
| Question | Generated column | Trigger |
|---|---|---|
| Does the value depend only on columns in the same row? | Suitable if the expression is immutable and meets the other generation-expression rules. | Possible, but usually requires more procedural logic to maintain. |
| Does the rule need another table, a subquery, or mutable state? | No. Generation expressions cannot use these. | Can implement procedural behavior beyond the scope of a generation expression. |
| Can a caller supply or override the derived value? | No. A generated column cannot be assigned directly by an INSERT or UPDATE caller. | A trigger can change the incoming row according to its logic. |
| When is the value computed? | Virtual: when read. Stored: when the row is written. | At the configured trigger event and timing. |
| What needs review? | PostgreSQL major version, expression restrictions, storage mode, and replication configuration. | Trigger timing, covered events, firing order, and consistency across write paths. |
These are capability differences, not a claim that every trigger design is equivalent or safer. PostgreSQL documents the generated-expression rules and trigger behavior in its Generated Columns and Overview of Trigger Behavior documentation.
When a generated column fits
A generated column is the direct choice for a derived value that can be expressed as an immutable calculation over the current row. PostgreSQL computes the value, so application code does not need to keep a second copy synchronized on every write. The column cannot be directly set by an INSERT or UPDATE caller.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →The generation expression has important boundaries: it must use immutable operations, cannot contain a subquery, cannot refer to another table, and cannot reference another generated column. If the derivation crosses any of those boundaries, it does not qualify as a generated-column expression. See PostgreSQL’s generated-column restrictions and CREATE TABLE documentation.
#1 Best Overall
Virtual and stored behavior in PostgreSQL 18
PostgreSQL 18 supports both virtual and stored generated columns. A virtual value is calculated when read and does not occupy row storage as a materialized value. A stored value is calculated when the row is written and occupies storage. Virtual is the PostgreSQL 18 default, so specify the kind explicitly when the storage behavior matters.
PostgreSQL 17 and earlier version checks
PostgreSQL 17 supports stored generated columns only. If code must run on PostgreSQL 17, declare the column STORED. Before relying on virtual generated columns or PostgreSQL 18 replication behavior, verify the server’s major version. The version distinction is documented in the PostgreSQL 17 Generated Columns documentation and the PostgreSQL 18 release notes.
Rank #2
When a trigger is the better fit
Choose a trigger when the derivation needs procedural handling or information outside the current row, or when the result must be managed through event-specific logic. Triggers can modify an incoming row at supported timing points, giving them flexibility that a generated expression does not have.
That flexibility comes with operational responsibility: maintain the procedural logic for every relevant write path, and review it alongside other triggers on the relation. PostgreSQL fires triggers of the same kind for the same event in alphabetical order by trigger name, so ordering can affect a design in which one trigger changes base values used by another. See trigger behavior and CREATE TRIGGER.
How generated columns interact with triggers
For stored generated columns, PostgreSQL computes the generated value after BEFORE triggers and before AFTER triggers. A BEFORE trigger may change base columns before that computation, but it must not try to read the new generated value. An AFTER trigger can inspect the generated value. PostgreSQL 18 virtual generated columns are not computed when triggers fire.
Trigger event filters have a related detail: an UPDATE OF trigger can fire when an updated column is one on which a listed generated column depends. Account for that dependency when defining which updates should invoke trigger logic. The timing and event rules are described in the trigger behavior documentation and CREATE TRIGGER reference.
Performance, storage, and replication considerations
Stored generated columns use storage and perform their calculation on writes; virtual generated columns avoid storing a duplicate but calculate the value on reads. Those differences create different read and write cost profiles, but the PostgreSQL documentation does not establish a universal performance winner. Benchmark with representative data and workload, considering read frequency, write frequency, expression cost, and whether the value needs an index.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Replication also depends on version and configuration. PostgreSQL 18 logical replication can publish stored generated columns when configured through publish_generated_columns or a publication column list. Before PostgreSQL 18.0, logical replication did not publish generated columns. Consult PostgreSQL’s Generated Column Replication documentation when planning a publication.
Quick Recap
Decision checklist
- Use a generated column for an immutable, same-row calculation that callers should not set directly.
- Use a trigger when the rule needs procedural logic, other data, or event-specific row changes.
- On PostgreSQL 17, generated columns must be stored; on PostgreSQL 18, choose virtual or stored intentionally.
- Review trigger timing and ordering if triggers modify the base columns involved in a derivation.
- Measure your own read and write workload rather than assuming one option is faster.
- Check PostgreSQL 18 logical replication settings if stored generated values must be published.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
