PostgreSQL Generated Columns: Moving Derived Fields from Application Code into the Schema

The drift problem

You store first_name and last_name in a customers table. Your application computes full_name and writes it alongside the source columns. It works—until a second service updates the row and forgets to recompute the derived field, or a partial UPDATE changes only last_name. Suddenly full_name is stale, and every downstream report inherits the error.

Application-computed derived fields are a consistency liability. They duplicate derivation logic across every writer, and nothing in the database enforces that the derived value matches its sources.

The thesis: let the schema own the derivation

PostgreSQL 12 introduced generated columns with the syntax GENERATED ALWAYS AS (expression) STORED. A generated column is computed by the database on every INSERT or UPDATE that touches its referenced columns, physically written to the table, and maintained transactionally with the row. Because the value is derived inside the same transaction, concurrent writers cannot leave it stale.

PostgreSQL supports only STORED generated columns; there is no VIRTUAL variant. The derived value occupies storage like any other column, and you can index it. You cannot insert into or update a generated column directly—the server rejects it.

Worked example: a normalized search field

Suppose you need a case-insensitive lookup on customer email plus a display name. Run these statements in psql or any SQL client connected as a user with CREATE privilege on the target schema.

-- Confirm the server major version
SHOW server_version;

If the result is 12 or newer, create a scratch table:

CREATE TABLE customers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name text NOT NULL,
    last_name text NOT NULL,
    email text NOT NULL,
    full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
    email_normalized text GENERATED ALWAYS AS (lower(email)) STORED
);

Insert source values only. The generated columns are populated automatically:

INSERT INTO customers (first_name, last_name, email)
VALUES ('Ada', 'Lovelace', '[contact removed]');

Query the derived columns:

SELECT full_name, email_normalized FROM customers;

You should see full_name as Ada Lovelace and email_normalized as [contact removed]. Now confirm the column is read-only:

UPDATE customers SET full_name = 'Grace Hopper' WHERE id = 1;

The server rejects the statement because generated columns cannot be assignment targets. To make the normalized email fast to search, add an index:

CREATE INDEX idx_customers_email_normalized ON customers (email_normalized);

Compare plans with and without the index:

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email_normalized = '[contact removed]';

With the index, the plan should switch from a sequential scan to an index scan. Without it, the database must read every row. The exact plan shape depends on table size and statistics.

If you no longer need the scratch table, remove it with DROP TABLE customers;. This also drops the index you created on it.

Trade-offs and limitations

Generated columns are not free. Every INSERT or UPDATE that modifies a referenced column evaluates the expression and writes the result. For high-write tables, measure the overhead before adopting them broadly.

The expression language is deliberately restricted. You cannot use subqueries, reference other rows, or depend on volatile state. Function volatility and availability rules are version-sensitive; confirm the exact restrictions against your target PostgreSQL major version before relying on a particular expression.

They also cannot replace triggers. If a derived value depends on another table, an aggregate over multiple rows, or session state, a trigger—or application logic—remains necessary.

Indexes on generated columns improve read performance but increase storage and write maintenance, just like any other index.

Approach	Consistency	Write cost	Best for
Application-computed	Risk of drift across writers	None in database	Single writer, simple derivations
Generated column	Atomic with row	Expression evaluated per write	Row-local derivations
Trigger	Atomic with row	Function call per write	Cross-table or complex logic

Actionable checklist

1. Verify the server version with SHOW server_version; and confirm it is 12 or newer.
2. Identify derived fields that depend only on columns in the same row.
3. Prototype in a scratch schema: create the table, insert source values, query the derived column, and attempt a direct UPDATE to confirm it is read-only.
4. Use EXPLAIN ANALYZE to measure whether an index on the generated column actually reduces query cost for your workload.
5. Benchmark write throughput with and without the generated column on a representative data set.

Generated columns shift a class of consistency bugs from application code into the schema. They are the right tool when the derivation is simple, row-local, and read frequently—but they are not a universal replacement for triggers or application logic.