Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsUse a PostgreSQL generated column when a value is an immutable calculation of columns in the same row. Use a trigger when the rule needs procedural logic, data outside that row, or custom event handling. The key constraints are your PostgreSQL version and whether the result should be computed on read or stored on write.
How to choose
| Requirement | Generated column | Trigger |
|---|---|---|
| Derivation uses only columns in the same row | Fits if the expression is immutable and meets PostgreSQL’s other restrictions. | Can do this too, but adds procedural logic to maintain. |
| Rule needs another table, a subquery, or mutable state | Not allowed in a generation expression. | Can implement procedural behavior beyond the generation-expression rules. |
| Caller must be able to supply or override the derived value | Not suitable: callers cannot directly assign a generated column. | Can modify the incoming row according to trigger logic. |
| When the value is computed | Virtual: when read. Stored: when written. | At the configured trigger event and timing. |
| Primary design checks | Version support, expression restrictions, storage mode, and replication behavior. | Timing, event coverage, trigger ordering, and consistency across write paths. |
A trigger is not automatically safer or equivalent to a generated column. Choose it only when its event-driven flexibility is needed, and account for every relevant way rows can be written.
As an Amazon Associate I earn from qualifying purchases.
What PostgreSQL generated columns allow
PostgreSQL computes a generated column; an INSERT or UPDATE caller cannot assign its value directly. A generation expression must use immutable operations and refer only to columns in the same row. It cannot contain a subquery, read another table, or refer to another generated column. See the PostgreSQL documentation on generated columns and CREATE TABLE.
PostgreSQL 18 supports two storage modes. A VIRTUAL column is calculated when read and does not store the derived value in the row; a STORED column is calculated on write and occupies storage. In PostgreSQL 18, virtual is the default, so specify the mode when the distinction matters. For example:
#1 Best Overall
CREATE TABLE products (
price numeric NOT NULL,
tax_rate numeric NOT NULL,
total numeric GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);This illustrates the syntax and same-row pattern; it is not a performance recommendation. The expression must still meet PostgreSQL’s immutability and other rules.
What triggers add
A trigger runs in response to a configured database event and timing. A row-level trigger can use procedural code to modify the incoming row at supported timing points, which makes triggers an option when the derivation needs behavior that a generated expression cannot express. PostgreSQL documents trigger timing and behavior in its overview of trigger behavior and CREATE TRIGGER reference.
Rank #2
That flexibility comes with an operational obligation: the trigger must cover the relevant events and write paths, and its logic must remain consistent with the data rule. Review it alongside other triggers on the table. When multiple triggers exist for the same event and relation, PostgreSQL fires them in alphabetical order by name. A trigger that derives a value from other trigger-modified data therefore needs deliberate naming and timing.
How generated columns interact with triggers
For stored generated columns, PostgreSQL computes the value after BEFORE triggers and before AFTER triggers. A BEFORE trigger can change the base columns before the generated value is calculated, but cannot read the new generated value. An AFTER trigger can inspect it. PostgreSQL 18 virtual generated columns are not computed when triggers fire, so a trigger cannot use them as though they were already materialized.
There is also a less obvious event-filter detail: an UPDATE OF trigger may fire when an updated column is one on which a listed generated column depends. Check the CREATE TRIGGER documentation when filtering update events around generated values.
Check your PostgreSQL version
PostgreSQL 17 implements stored generated columns only. PostgreSQL 18 adds virtual generated columns and makes virtual the default. If you require the value to be materialized on write, use STORED explicitly, particularly in SQL intended to run on PostgreSQL 18. PostgreSQL’s PostgreSQL 17 documentation, current generated-column documentation, and PostgreSQL 18 release notes describe the version difference.
Before choosing syntax, check the running server’s major version rather than assuming the version used in development is also deployed. A statement that relies on virtual columns will not work on PostgreSQL 17.
Account for logical replication
In 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. If subscribers depend on the derived value, verify the publication configuration and version behavior in the generated-column replication documentation.
Think about workload, not a universal speed claim
Stored generated columns use storage and compute on writes; virtual generated columns avoid storing a duplicate and compute on reads. Those differences affect where work occurs, but PostgreSQL documentation does not establish one option as universally faster. Compare the expression cost, read and write frequency, indexing needs, and actual workload before deciding. A trigger also adds procedural work at its configured events, so measure the design under representative conditions rather than inferring a winner from the feature name.
Quick Recap
Practical decision checklist
- Choose a generated column for a deterministic, immutable calculation that uses only same-row columns and should not be caller-overridden.
- Choose
VIRTUALin PostgreSQL 18 when computing on read is appropriate; chooseSTOREDwhen you need the value materialized on write or need stored-column replication support. - Use a trigger when the rule needs other data or procedural/event logic, and document which writes, timings, and other triggers affect it.
- Check major-version compatibility, trigger visibility and ordering, and replication requirements before deployment.
- Benchmark read/write behavior with your workload if performance drives the choice; neither mechanism has a universal documented speed advantage.
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.
