A PostgreSQL generated column is the better choice for any derived value it can express, because the database computes it on every write and refuses to let anyone set it by hand. A trigger is the fallback for the values a generated column is not allowed to compute: anything that reads another table, the clock, or a function PostgreSQL does not mark immutable.

The choice matters more than it looks, and it matters twice. Once when you write the table, because the two differ in cost, in what they allow, and in how they fail. And again when someone else inherits it: a generated column says what it is in the table definition, while a trigger-maintained column looks exactly like a column the application writes. Every PostgreSQL statement below was run on PostgreSQL 18.3 in a throwaway container.

What is the difference between a generated column and a trigger in PostgreSQL?

Take an order_lines table where line_total is unit_price * quantity. As a generated column it is one line:

CREATE TABLE order_lines (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    unit_price numeric(12,2) NOT NULL,
    quantity   integer NOT NULL,
    line_total numeric(12,2) GENERATED ALWAYS AS (unit_price * quantity) STORED
);

As a trigger it is a plain column plus a function and a trigger that keeps it up to date (drop the first table before running this one, since both are called order_lines):

CREATE TABLE order_lines (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    unit_price numeric(12,2) NOT NULL,
    quantity   integer NOT NULL,
    line_total numeric(12,2)
);

CREATE FUNCTION set_line_total() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.line_total := NEW.unit_price * NEW.quantity;
    RETURN NEW;
END $$;

CREATE TRIGGER order_lines_total BEFORE INSERT OR UPDATE OF unit_price, quantity
    ON order_lines FOR EACH ROW EXECUTE FUNCTION set_line_total();

Both produce 29.97 for a price of 9.99 and a quantity of 3. The differences are everywhere else:

The insert timings are the average of three runs each of one INSERT ... SELECT over generate_series(1, 1000000) on a laptop, so read them as ratios: a row-level PL/pgSQL trigger added about 72% to the plain insert, the stored generated column about 16%, and the virtual one nothing measurable, because it writes nothing.

Should I use a generated column or a trigger for a derived column?

Use a generated column when every input is in the same row and every function is immutable. That covers arithmetic like line_total, normalised copies like lower(email), and extracted values like a tsvector built with to_tsvector('english', body) or a field pulled out of a jsonb document. The one-argument to_tsvector(body) is rejected, because it depends on a server setting. PostgreSQL enforces the rules itself when you create the column:

• GENERATED ALWAYS AS (now()) STORED fails with generation expression is not immutable.
• A subquery fails with cannot use subquery in column generation expression.
• A generated column that reads another generated column fails with cannot use generated column "b" in column generation expression.

The third one has an easy way round: repeat the other column’s expression inline. The first two are the cases where a trigger is the right answer. The common ones are an updated_at set to now() on every update, an orders.total that sums order_lines, and a denormalised copy of a parent’s value, such as a customer’s name stamped onto an invoice. None of them can be a generated column, and each of them is a trigger or a query.

If a generated column can express the value, the trigger buys nothing but risk. The table above shows three kinds of it.

A trigger can be bypassed. With the trigger declared BEFORE INSERT OR UPDATE OF unit_price, quantity, running UPDATE order_lines SET line_total = 5 succeeds and leaves line_total at 5.00 against a price of 9.99 and a quantity of 3, because the statement touched neither column the trigger listens to. Leave out UPDATE OF and the trigger fires on every update, which fixes this and costs a function call on updates that never touch the price.

A trigger can be switched off. SET session_replication_role = replica, which the PostgreSQL documentation says logical replication systems set while applying changes, stops ordinary triggers from firing. An insert under it left line_total NULL. Setting it needs superuser or a granted SET privilege, and ALTER TABLE ... DISABLE TRIGGER switches a trigger off the same way. A generated column is computed regardless.

A trigger fails late. PostgreSQL records that the trigger depends on the columns named in UPDATE OF, so dropping unit_price is refused. But it does not read the function body. Renaming unit_price to price succeeds, and the next insert fails with record "new" has no field "unit_price". The migration passed; the application broke on its next write.

Stored or virtual: which generated column does PostgreSQL 18 give you?

PostgreSQL 12 introduced generated columns as stored only. PostgreSQL 18 added virtual ones and made them the default, so GENERATED ALWAYS AS (unit_price * quantity) with no keyword now computes the value when a row is read and stores nothing. The generated columns documentation lists what a virtual column gives up: it cannot use user-defined functions or types, and logical replication can publish only stored ones. Creating an index on one fails with indexes on virtual generated columns are not supported.

The difference shows most when you add the column to a table that already has data. On the same 1,000,000-row table:

Each timing is the same in three runs to within a few percent. The stored column took the table from 50 MB to 58 MB on disk and the virtual one left it at 50 MB, while the backfill doubled it to 107 MB until vacuum reclaims the old row versions. Changing the expression later is ALTER TABLE ... ALTER COLUMN line_total SET EXPRESSION AS (...), available since PostgreSQL 17. For a stored column it rewrites the table again under the same lock; for a virtual one it is a catalogue change.

A virtual column cannot be indexed itself, but that matters less than it sounds: PostgreSQL expands it when it plans a query, so in the same container an index on the expression itself served a WHERE on the virtual column. So the rule inside the rule: virtual when the value is cheap to compute, stored when computing it on every read costs more than storing it, or when a logical replication subscriber needs the value.

How do I find the computed columns in a database I did not write?

A generated column is in the catalogue, so one query finds all of them:

SELECT a.attrelid::regclass AS table_name, a.attname,
       CASE a.attgenerated WHEN 's' THEN 'stored' ELSE 'virtual' END AS kind,
       pg_get_expr(d.adbin, d.adrelid) AS expression
FROM pg_attribute a
JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attgenerated <> '' AND NOT a.attisdropped;

A trigger-maintained column is not. The catalogue knows which triggers exist and which columns their UPDATE OF names, but not which column a function writes, so the only way to find line_total is to list the triggers and read each function body:

SELECT tg.tgrelid::regclass AS table_name, tg.tgname, p.proname, p.prosrc
FROM pg_trigger tg
JOIN pg_proc p ON p.oid = tg.tgfoid
WHERE NOT tg.tgisinternal;

That asymmetry is the strongest argument for the generated column. To understand your schema, you need to know which values the application writes and which the database derives, and only one of the two approaches tells you without reading code.

How Schemity shows generated columns and the triggers behind a column

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git.

When you connect a PostgreSQL database, Schemity reads its generated columns, as it does on MySQL, SQLite, and SQL Server, where they are called computed columns. On the canvas, a generated column’s default cell shows = and its expression next to its type, so line_total reads = unit_price * (quantity)::numeric, the expression as PostgreSQL stores it. When Schemity sizes an entity to fit its content, it makes room for the first 24 characters of an expression, so one long expression does not stretch the table; widen the entity and the rest appears. SVG export draws it the same way.

Open the column and the field dialog shows the full expression and says whether the database stores the value or computes it when read. The name and the description stay editable. Everything else is greyed out, including the type, the default, precision and scale, and the key and nullable checkboxes, because the expression and what follows from it belong to the database.

The diagram does not draw triggers, so a trigger-maintained line_total looks like a plain column there, the same as in psql: a nullable NUMERIC(12,2) with NULL in its default cell, like any nullable column with no default.

Triggers appear where they cost you something, in impact analysis. Rename quantity in a diagram of either version of the table and the report lists every object that depends on it and what PostgreSQL will do to each:

• For the generated column: “generated column line_total depends on order_lines.quantity. The database updates it to match.”
• For the trigger: “trigger order_lines_total depends on order_lines.quantity. It fails the next time it runs if it uses what changed.”

Here is the generated column’s version in Preview changes. The canvas still shows the expression as the database holds it today, (quantity), because PostgreSQL only rewrites it to qty when the rename runs; a re-sync afterwards shows the new text.

The same rename on the trigger version gets a different verdict. PostgreSQL follows the rename in the trigger’s UPDATE OF list, but not inside the function body, so the finding says the trigger may fail on its next run rather than that anything is updated to match.

Dropping a column that a generated column reads is reported as refused, since PostgreSQL, MySQL, SQL Server and SQLite all refuse it. The drop still shows as losing the column’s data, and the second finding says the statement will not get that far:

Apply it anyway and PostgreSQL returns cannot drop column quantity of table order_lines because other objects depend on it, with the detail line column line_total of table order_lines depends on column quantity of table order_lines, and the transaction rolls back with both columns still in place.

Changing its type is refused on PostgreSQL and SQL Server, and goes through on MySQL, and on SQLite through the table rebuild a type change needs there; the report says which. The same report runs on a migration file written by Django, Rails, Prisma or an AI agent before it is applied. If you inherited the schema, write down what each trigger maintains as a column description in the diagram, where the next reader will see it.

Related reading

The updated_at column is the most common trigger in any schema, and whether it should be timestamp or timestamptz is in timestamp vs timestamptz. The lock that a stored generated column takes on an existing table is the same one covered in unique constraint vs unique index. And for mapping a legacy database you did not write, start with grouping its tables by domain.