Generated Columns

Definition

A generated (a.k.a. computed) column's value is automatically derived from an expression over other columns in the same row, rather than being supplied directly by an INSERT/UPDATE. It exists to keep derived data consistent without relying on application code or triggers.

sql
-- PostgreSQL (STORED is currently the only supported kind)
CREATE TABLE order_items (
    id       SERIAL PRIMARY KEY,
    qty      INT NOT NULL,
    price    NUMERIC(10,2) NOT NULL,
    total    NUMERIC(12,2) GENERATED ALWAYS AS (qty * price) STORED
);

-- MySQL (supports both STORED and VIRTUAL)
CREATE TABLE order_items (
    qty   INT,
    price DECIMAL(10,2),
    total DECIMAL(12,2) AS (qty * price) STORED
);

-- SQL Server ("computed column")
CREATE TABLE order_items (
    qty   INT,
    price DECIMAL(10,2),
    total AS (qty * price) PERSISTED
);

STORED vs VIRTUAL

• STORED / PERSISTED: the computed value is physically written to disk alongside the row, recalculated whenever a source column changes. It can be indexed like a normal column, at the cost of extra storage.
• VIRTUAL: the value is computed on read and not stored at all (MySQL and SQL Server support this; PostgreSQL currently only supports STORED). Saves space but adds CPU cost per read and, in some engines, cannot be indexed directly (SQL Server can index some virtual computed columns).

Worked Example

A useful, realistic use is building a searchable full name from parts, and indexing it:

sql
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name  TEXT NOT NULL,
    full_name  TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

CREATE INDEX idx_users_full_name ON users (full_name);

SELECT * FROM users WHERE full_name = 'Ada Lovelace';   -- uses the index, no runtime concatenation needed

Edge Cases and Pitfalls

• You cannot write to a generated column directly: INSERT INTO order_items (qty, price, total) VALUES (2, 9.99, 19.98); raises an error in PostgreSQL — total must be omitted, always letting the engine compute it.
• Dependency locking: you generally cannot drop or change the type of a column that a generated column depends on without first altering/dropping the generated column.
• Expression restrictions: the expression must typically be deterministic — no subqueries, no calls to other tables, and often no volatile functions (e.g. now(), random()) are allowed, since the value must be reliably reproducible from the row's own columns.
• Not free: STORED generated columns consume real disk space and add write-time CPU cost on every insert/update to source columns.

Key Takeaways / Q&A

Q: Can you INSERT a value directly into a generated column? A: No — its value is always derived by the engine from its expression; attempting to supply one explicitly raises an error.

Q: Why choose STORED over VIRTUAL for a generated column you plan to query often? A: STORED values can be indexed directly and avoid recomputation on every read, trading extra storage for query performance.

Q: What happens if you try to drop a column referenced by a generated column's expression? A: Most engines block it (or require you to drop/alter the generated column first), since the expression would no longer be computable.