PostgreSQL Generated Column vs Trigger: Which to Use for a Derived Column
Disclosure: I build Schemity , a desktop ERD tool - this post is from our blog and uses it for the examples. TL;DR: Use a generated column when the value comes from other columns in the same row through immutable functions, because the database guarantees it and nobody can write to it. Use a trigger only when the value needs another table, the current time, or a function PostgreSQL does not call…
PostgreSQL offers two ways to handle derived columns: generated columns and triggers. A generated column automatically computes the value from other columns in the same row using immutable functions. This guarantees that the value cannot be modified manually and that the database computes it every time a row is written. An example of using a generated column is creating a table called order_lines with a line_total column that multiplies unit_price and quantity.
On the other hand, a trigger is a function that runs before or after certain database events, such as INSERT or UPDATE. A trigger can read from other tables, use the current time, or execute functions that PostgreSQL does not consider immutable. By default, a trigger can be written to manually set the derived column's value, but it will not take effect until the next trigger execution.
Triggers also offer more flexibility than generated columns, as they can read from other tables or tables in UPDATE OF, and they can be disabled or dropped without failing the entire statement, as long as the change doesn't affect the columns the trigger depends on.
When deciding between a generated column and a trigger, it's crucial to consider the nature of the derived value and the specific requirements of the application. If the value can be computed using immutable functions and is derived solely from other columns in the same row, a generated column is the better choice. This ensures the data remains consistent and the database handles the computation efficiently.
However, if the derived value requires data from other tables, the current time, or functions not marked as immutable, a trigger is the way to go. Triggers also provide more flexibility for handling complex computations, such as updating timestamps on every update, summing order line totals, or denormalizing parent values like a customer's name on invoices.
Ultimately, the choice between a generated column and a trigger depends on the specific use case and the tradeoffs between performance, flexibility, and data consistency. Using generated columns when possible can simplify the table definition and reduce the risk of errors or manual modifications. However, triggers offer more functionality and can be switched off or bypassed when necessary, making them a valuable tool for handling complex derived values in PostgreSQL.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.