Generated Columns in Practice

1. What a generated column is and what problem it solves

A generated column is a column whose value the database automatically computes from an expression over other columns of the same row, instead of the application writing that value itself. Instead of manually computing a derived value such as a gross price or a full name on every INSERT and UPDATE in application code and keeping it in sync, a generated column defines the computation once in the schema, and the database guarantees the value is always correct and current.

The problem a generated column solves is data duplication and drift: without it, either every application that writes must reimplement the same computation logic, risking different, inconsistent results, or a trigger takes over the computation with the added maintenance burden of a procedural function. A generated column, by contrast, is purely declarative, directly visible in the CREATE TABLE or ALTER TABLE, and guarantees consistency regardless of access path, just like a constraint.

The SQL standard knows two variants of a generated column, STORED and VIRTUAL, which differ fundamentally in whether the computed value is physically stored on disk or recomputed on every read access. The following sections show practical use cases for both variants, from simple derived values to full-text search indexes.

2. STORED vs VIRTUAL generated columns

A STORED generated column computes its value on every INSERT and UPDATE and physically stores the result like a normal column. This means extra storage space and slightly more write effort, but in return the value is immediately available on every read access without recomputation, and it can be indexed directly like any other column.

A VIRTUAL generated column, by contrast, does not store a value but recomputes it on every SELECT, similar to a view at the column level. This saves storage space and write effort but costs computation time on every read access and cannot be indexed directly in some database systems. MySQL supports both variants via GENERATED ALWAYS AS (expression) STORED or VIRTUAL, with VIRTUAL being the default when no keyword is given. PostgreSQL supports only STORED generated columns up through version 17, VIRTUAL generated columns were only added with PostgreSQL 18.

-- MySQL: both STORED and VIRTUAL are supported, VIRTUAL is the implicit default
CREATE TABLE product (
    product_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
    net_price     DECIMAL(10,2) NOT NULL,
    vat_rate      DECIMAL(4,2) NOT NULL DEFAULT 19.00,
    -- computed on every read, no extra storage
    gross_price_virtual DECIMAL(10,2)
        GENERATED ALWAYS AS (net_price * (1 + vat_rate / 100)) VIRTUAL,
    -- computed on write, stored physically, directly indexable
    gross_price_stored DECIMAL(10,2)
        GENERATED ALWAYS AS (net_price * (1 + vat_rate / 100)) STORED
);

-- PostgreSQL: only STORED is available up to and including version 17
CREATE TABLE product_pg (
    product_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    net_price     NUMERIC(10,2) NOT NULL,
    vat_rate      NUMERIC(4,2) NOT NULL DEFAULT 19.00,
    gross_price   NUMERIC(10,2)
        GENERATED ALWAYS AS (net_price * (1 + vat_rate / 100)) STORED
);

3. Computing derived values in practice

The most common practical use case for a generated column is a simple, deterministic value computed from a few other columns, such as a full name from first and last name, a gross price from net price and tax rate, or a normalized version of a text field. These patterns replace application code that would otherwise have to repeat the same computation on every read, or a trigger that would have to maintain the value separately on every write.

An important practical advantage: a generated column can be used in WHERE, ORDER BY, and GROUP BY clauses just like any regular column, which would not be possible with pure application logic without database support. A search or sort by full name therefore works directly at the database level, without the application having to translate the duplicated computation into an SQL query.

-- PostgreSQL: full name as a stored generated column, directly queryable
CREATE TABLE customer (
    customer_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name   VARCHAR(100) NOT NULL,
    last_name    VARCHAR(100) NOT NULL,
    full_name    VARCHAR(201)
        GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

INSERT INTO customer (first_name, last_name) VALUES ('Anna', 'Schmidt');

-- The generated column can be used like any regular column
SELECT customer_id, full_name FROM customer
WHERE full_name ILIKE 'anna%'
ORDER BY full_name;

4. Full-text search indexes with generated columns

One of the most elegant applications of a generated column is maintaining a full-text search index in PostgreSQL. The tsvector data type represents text that has already been broken down into searchable, stemmed lexemes, and is typically produced via the to_tsvector function from one or more text fields. Without a generated column, this conversion would either need to be recomputed on every search query, which is slow on large tables, or maintained via a trigger on every write.

A STORED generated column with a GIN index on the tsvector value combines both advantages: the computation happens once at write time, the index then makes searching extremely fast afterward, and the entire logic is declaratively visible in the schema instead of hidden in a separate trigger. This pattern is considerably less maintenance-heavy than the traditional trigger-based maintenance of a tsvector field, which was the standard approach before generated columns were introduced in PostgreSQL.

-- PostgreSQL: full-text search index maintained via a generated column
CREATE TABLE article (
    article_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       TEXT NOT NULL,
    body        TEXT NOT NULL,
    search_vector TSVECTOR
        GENERATED ALWAYS AS (
            setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
            setweight(to_tsvector('english', coalesce(body, '')), 'B')
        ) STORED
);

CREATE INDEX idx_article_search ON article USING gin (search_vector);

-- Fast full-text search using the maintenance-free generated column
SELECT article_id, title
FROM article
WHERE search_vector @@ websearch_to_tsquery('english', 'database trigger')
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', 'database trigger')) DESC;

5. Generated columns combined with indexes

A STORED generated column can be given a regular B-tree index, which in many database systems is a more elegant alternative to a functional index (also called an expression index). A functional index on LOWER(email) requires every query to use exactly the same expression in the WHERE clause for the index to be used, whereas a generated column with the same expression can be referenced directly as a regular, named column in every query, which considerably improves readability.

An additional advantage: an indexed generated column can also be combined with a UNIQUE constraint, for example to enforce case-insensitive uniqueness of an email address, without the application itself having to ensure consistent lowercasing before every INSERT. This pattern combines the advantages of constraints and generated columns into a single, declarative solution.

-- PostgreSQL: generated column as a readable, indexable, unique alternative
-- to a functional index on LOWER(email)
CREATE TABLE account (
    account_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email         VARCHAR(255) NOT NULL,
    email_normalized VARCHAR(255)
        GENERATED ALWAYS AS (lower(trim(email))) STORED,
    CONSTRAINT uq_account_email_normalized UNIQUE (email_normalized)
);

-- Readable, named column instead of repeating LOWER(TRIM(email)) everywhere
SELECT * FROM account WHERE email_normalized = lower(trim('Anna@Example.com '));

6. Limits: what does not work in a generated column

The expression of a generated column, similar to a CHECK constraint, may only refer to columns of the same row, never to other rows or other tables. An aggregation across several rows, such as a running total, therefore cannot be expressed as a generated column, for that window functions in a regular query or a materialized view are the appropriate tools.

Likewise, the functions used must be deterministic: CURRENT_TIMESTAMP, RANDOM(), or a call to a function not marked IMMUTABLE are forbidden for generated columns in most systems, for the same reasons as with CHECK constraints using function calls. A generated column also must not reference another generated column of the same table, the circular dependency would make the computation order undefined.

7. Adding generated columns to existing tables

Adding a STORED generated column to an already populated table requires the database to compute and store the computed value for every existing row once. On a very large table with many millions of rows, this one-time rewrite can take noticeably long and, in PostgreSQL as well as older MySQL versions, requires an exclusive table lock, causing brief downtime during migration.

For production systems with high availability requirements, it is common to perform such a migration outside peak load times, or, for very large tables, to use an online schema change tool that rewrites the table in batches instead of running a single, long-running, locking operation. A VIRTUAL generated column avoids this problem entirely, because no rewrite of existing data is needed, but it lacks the ability to be indexed directly in most systems.

-- MySQL: adding a STORED generated column to an existing, populated table
-- triggers a full table rewrite, plan for a maintenance window on large tables
ALTER TABLE product
    ADD COLUMN gross_price DECIMAL(10,2)
    GENERATED ALWAYS AS (net_price * (1 + vat_rate / 100)) STORED;

-- Verify the migration computed correct values for existing rows
SELECT product_id, net_price, vat_rate, gross_price
FROM product
LIMIT 10;

8. Vendor differences: MySQL, PostgreSQL, Oracle

MySQL has supported both STORED and VIRTUAL generated columns since version 5.7 via the unified GENERATED ALWAYS AS syntax. PostgreSQL was limited to STORED through version 17, VIRTUAL generated columns only arrived with PostgreSQL 18. Oracle traditionally calls the concept a virtual column and has supported it since version 11g, defaulting to VIRTUAL-like behavior, with the option to additionally index the column, which effectively corresponds to STORED behavior for indexing purposes.

These differences matter for portability decisions: a schema designed for MySQL with VIRTUAL generated columns must be switched to STORED when migrating to PostgreSQL 17 or older, with the corresponding extra storage requirement. Anyone planning platform-independent migrations should therefore commit to STORED as the lowest common denominator from the start, instead of relying on the VIRTUAL variant.

9. Generated column vs trigger vs view

The choice between a generated column, a trigger, and a view depends on the scope of the computation and whether several rows or tables need to be involved. A generated column is the right choice for deterministic, row-local computations. A trigger takes over more complex logic that involves other tables. A view is suited for computations that aggregate over several rows or require joins across multiple tables, without a physical value needing to be stored.

10. Summary

A generated column computes derived values declaratively in the schema, without application code or a trigger duplicating the computation. STORED physically stores the value and is indexable, VIRTUAL recomputes it on every read and saves storage space. Practical use cases range from simple derived values such as a gross price to low-maintenance full-text search indexes with tsvector in PostgreSQL.

The limits lie in cross-row aggregations and non-deterministic expressions, here views, window functions, or triggers take over. MySQL has supported both variants for a long time, PostgreSQL only added VIRTUAL from version 18. Anyone adding a generated column to a large existing table should factor the required rewrite and its associated lock into migration planning.

11. FAQ: Generated Columns in Practice