PostgreSQL Generated Columns vs Triggers: How to Choose

Choose a PostgreSQL generated column when derived data is a deterministic expression of values in the same row and should always be computed on read or write by the database. Choose a trigger when derivation needs procedural logic, changes other rows or tables, or must run as part of a broader write workflow; for many cross-row summaries, however, an explicit query or maintained summary table is clearer.

What counts as derived data?

Derived data is a value calculated from other stored values: a line total from quantity and unit price, a normalized search key from a name, or a status timestamp recorded when an event occurs. The important question is not simply whether a value can be calculated. It is whether it is a pure, row-local calculation or a side effect with broader behavior.

Storing derived values can make queries simpler or faster, but it creates a consistency obligation. Whenever source data changes, the derived value must remain correct. PostgreSQL offers generated columns for a constrained class of calculations and triggers for custom behavior. Application code can also calculate values, but then every writer must follow the same rules.

How PostgreSQL generated columns work

A generated column is declared with a generation expression. PostgreSQL currently supports VIRTUAL and STORED generated columns: a virtual value is computed when it is read, while a stored value is computed when its row is written and occupies storage. The default is VIRTUAL. Generated columns are read-only to ordinary SQL writes; PostgreSQL calculates their values from the expression.

For example, a table of order lines could include:

CREATE TABLE order_line (
  quantity integer NOT NULL,
  unit_price numeric(10, 2) NOT NULL,
  line_total numeric(12, 2)
    GENERATED ALWAYS AS (quantity * unit_price) STORED
);

Applications insert or update quantity and unit_price, not line_total. PostgreSQL keeps the result aligned with the row. Generated expressions must meet PostgreSQL’s restrictions: they can use only immutable functions and operators, cannot contain subqueries, and cannot refer to another generated column. They are intended for values based on the current row, not other rows or changing external state.

How triggers work

A trigger runs a trigger function when a specified database event occurs, such as inserting or updating a row. A BEFORE row trigger can alter the row being written; an AFTER trigger can perform follow-up work. Triggers can implement rules that a generated expression cannot, including conditional logic and coordinated writes to other tables.

For example, a trigger might update an audit table whenever an account’s email address changes, recording the old and new values. Another might maintain a summary row when detail records are inserted or deleted. These behaviors are not just calculations: they have timing, transaction, concurrency, and error-handling implications.

Triggers execute within the transaction that caused them. If a trigger’s work fails, the originating statement normally fails too. That atomicity can be useful for enforcing a database rule, but a trigger that performs unexpected extra writes can make ordinary updates harder to understand. Triggers may also call other triggers indirectly, so document the event and effects clearly.

How to choose

• Use a generated column for a stable calculation using only columns from that same row, such as a subtotal or a normalized representation that relies on immutable functions.
• Use a trigger when a write needs procedural decisions, must populate or validate other columns in a custom way, or must update related records as part of the same transaction.
• Use a view or query when the value should be calculated from multiple tables but does not need to be physically stored.
• Use a summary table or materialized view when a cross-row aggregate is expensive and can be refreshed or maintained under an explicit policy.
• Keep it in application code when it is presentation-only, is not an integrity rule, and centralizing it in the database would make the data model less clear. Ensure every writer still follows a consistent approach.

Start by asking where the inputs live and when the result must be current. If all inputs are in one row and the formula is stable, a generated column usually expresses the intent most directly. If the logic depends on other rows, time, user context, or a sequence of actions, a generated column is not a fit. Before choosing a trigger for that case, decide whether the value must be stored at all.

Trade-offs and common problems

Generated columns make the dependency visible in the table definition and prevent clients from supplying a conflicting value. A stored generated value costs disk space and adds work when source columns change; a virtual value avoids storing the result but requires computation when read. Indexing and querying generated values can be useful, but the expression and function restrictions still apply.

Triggers are more flexible, but that flexibility adds operational cost. A developer may overlook that a table has a trigger, or write a bulk update without realizing it invokes row-level work for every affected row. Trigger logic also needs tests for inserts, updates, deletes, null values, and concurrent transactions where relevant. Be careful about recursive behavior and about maintaining a summary correctly when rows are deleted or moved between groups.

A frequent misconception is that a trigger is automatically better because it can calculate anything. Flexibility does not guarantee correctness: a trigger-maintained total can drift if one write path is missed, if its logic handles only inserts, or if concurrent updates are not coordinated correctly. Likewise, a generated column is not a general substitute for a trigger; it cannot calculate from a table-wide sum or query another table.

Another concern is whether changing a function changes existing generated values. A generation expression is restricted to immutable functions precisely because PostgreSQL expects the result to depend only on its inputs. If the meaning of a calculation changes, plan a deliberate schema/data migration rather than assuming previously stored values will refresh themselves.

Alternatives for cross-row data

For a value such as “total purchases for this customer,” a normal view can calculate the aggregate from current rows without duplicating it. This keeps one authoritative source of truth, though each query pays the calculation cost. A materialized view stores query results and can be refreshed, so it may be appropriate when slight staleness is acceptable and refresh timing is managed.

A summary table maintained by triggers can provide immediately updated totals, but it requires careful handling of every operation that changes the underlying rows, including updates that move a row between customers. If that complexity is not warranted, calculate the total when needed or refresh a materialized view. Choose based on freshness, query cost, write volume, and the consequences of stale or incorrect data.

Frequently Asked Questions

Can a PostgreSQL generated column use another table?
No. A generated expression is limited to the current row and cannot use a subquery to look up another table. Use a query, view, or an explicitly maintained design for cross-table data.

Can I insert a value into a generated column?
Ordinary inserts and updates do not set its value; PostgreSQL computes it. Supply the source columns instead. Generated columns are not a place to store an application-provided override.

Should I use a trigger to update a timestamp?
Often, yes, if the timestamp means “when this row was last modified” and should be set automatically on updates. A generated column cannot represent the time an update occurred because that depends on an event, not just the row’s current values.

Are generated columns faster than triggers?
There is no universal winner. Generated columns are simpler for supported row-local expressions. Triggers can do more work and may add write overhead; actual performance depends on the expression, access patterns, and trigger actions. Measure representative workloads.

Can a trigger maintain totals across rows safely?
It can, but correctness under concurrent writes and all insert, update, and delete cases must be designed and tested. For many uses, a query or refreshed materialized view is simpler and less vulnerable to maintenance errors.

Can a generated column be indexed?
PostgreSQL supports indexes involving generated columns, subject to the usual index restrictions. An index can speed searches on the derived value, but it adds storage and write work; create one only when query patterns justify it.

Related

• AI Talking Photo Videos: Decisions to Make First
• Artificial Intelligence in Education, Traffic, Health and Coding
• Managed Node.js Hosting: What It Handles and What It Doesn’t