GENERATED COLUMNSDDL
Buy Me A Coffee

Columns whose values are automatically computed from an expression over other columns in the same row. The database maintains the value rather than the application. Also called computed columns or virtual columns.

Columns whose values are computed from an expression, either stored on disk or computed on read. Useful for denormalization, materialized reports, and enforcing computed invariants at the schema level. PostgreSQL, MySQL, and SQL Server support them.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.7 Two modes: VIRTUAL (computed on read, no storage) and STORED (computed on write, stored on disk). VIRTUAL is the default. Generated columns cannot reference other generated columns, use subqueries, or call non-deterministic functions. Can be indexed only if STORED (or as a functional index in MySQL 8.0).
PostgreSQL ✓ Supported 12 PostgreSQL 12 added GENERATED ALWAYS AS (...) STORED. Only STORED generated columns are supported — there is no VIRTUAL mode. The expression may reference other base columns but not other generated columns. Can be indexed normally. Prior to PG 12, use a trigger or a view to achieve similar results.
SQL Server ✓ Supported 2000 Called computed columns. Two modes: PERSISTED (stored) and non-persisted (virtual). Syntax uses AS expression rather than GENERATED ALWAYS AS. Persisted computed columns can be indexed and included in foreign keys. Non-deterministic expressions are allowed in non-persisted columns but not in persisted ones.
Oracle ✓ Supported 11g Called virtual columns. Syntax uses GENERATED ALWAYS AS (...) VIRTUAL. Only VIRTUAL mode (no STORED equivalent). Virtual columns cannot be stored physically but can be indexed (Oracle creates the index on the derived values). Cannot reference other virtual columns.
SQLite ✓ Supported 3.31.0 Both VIRTUAL and STORED modes supported since 3.31.0 (2020-01-22). VIRTUAL is the default. Expression must be deterministic. Generated columns cannot be used as part of the PRIMARY KEY. Cannot be indexed unless STORED.

Details

Generated column terminology differs by engine: MySQL and PostgreSQL use GENERATED ALWAYS AS, SQL Server uses AS (computed column), Oracle uses VIRTUAL column. The STORED vs VIRTUAL split is roughly universal but PostgreSQL only supports STORED — there is no VIRTUAL mode. SQL Server's PERSISTED keyword maps to STORED. All engines allow indexing stored/persisted generated columns; only SQL Server and PostgreSQL allow indexing virtual/non-persisted ones as functional indexes. None allow referencing other generated columns in the expression.

Standard Syntax

-- SQL standard (MySQL 5.7+, PostgreSQL 12+): CREATE TABLE products ( price DECIMAL(10,2), tax_rate DECIMAL(4,3), total DECIMAL(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED );

Version Support

MySQL: Since 5.7 PostgreSQL: Since 12 SQL Server: Since 2000 Oracle: Since 11g SQLite: Since 3.31.0

Per-Database Syntax & Notes

MySQL

Two modes: VIRTUAL (computed on read, no storage) and STORED (computed on write, stored on disk). VIRTUAL is the default. Generated columns cannot reference other generated columns, use subqueries, or call non-deterministic functions. Can be indexed only if STORED (or as a functional index in MySQL 8.0).

CREATE TABLE products ( price DECIMAL(10,2), quantity INT, subtotal DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity) VIRTUAL, subtotal_s DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity) STORED ); -- Index on STORED generated column: CREATE INDEX idx_subtotal ON products (subtotal_s); -- Add generated column to existing table: ALTER TABLE products ADD COLUMN tax DECIMAL(10,2) GENERATED ALWAYS AS (price * 0.1) STORED;

PostgreSQL

PostgreSQL 12 added GENERATED ALWAYS AS (...) STORED. Only STORED generated columns are supported — there is no VIRTUAL mode. The expression may reference other base columns but not other generated columns. Can be indexed normally. Prior to PG 12, use a trigger or a view to achieve similar results.

CREATE TABLE products ( price NUMERIC(10,2), quantity INTEGER, subtotal NUMERIC(10,2) GENERATED ALWAYS AS (price * quantity) STORED ); -- Index on generated column (works like any other column): CREATE INDEX idx_subtotal ON products (subtotal); -- Cannot INSERT/UPDATE a generated column directly: INSERT INTO products (price, quantity) VALUES (9.99, 5); -- subtotal is automatically 49.95

SQL Server

Called computed columns. Two modes: PERSISTED (stored) and non-persisted (virtual). Syntax uses AS expression rather than GENERATED ALWAYS AS. Persisted computed columns can be indexed and included in foreign keys. Non-deterministic expressions are allowed in non-persisted columns but not in persisted ones.

CREATE TABLE products ( price DECIMAL(10,2), quantity INT, subtotal AS (price * quantity), -- virtual subtotal_p AS (price * quantity) PERSISTED -- stored ); -- Index requires PERSISTED for non-simple expressions: CREATE INDEX idx_subtotal ON products (subtotal_p); -- Add to existing table: ALTER TABLE products ADD tax AS (price * 0.1) PERSISTED;

Oracle

Called virtual columns. Syntax uses GENERATED ALWAYS AS (...) VIRTUAL. Only VIRTUAL mode (no STORED equivalent). Virtual columns cannot be stored physically but can be indexed (Oracle creates the index on the derived values). Cannot reference other virtual columns.

CREATE TABLE products ( price NUMBER(10,2), quantity NUMBER, subtotal NUMBER(10,2) GENERATED ALWAYS AS (price * quantity) VIRTUAL ); -- Index on virtual column: CREATE INDEX idx_subtotal ON products (subtotal); -- Simplified syntax (VIRTUAL is default/only option): CREATE TABLE products ( price NUMBER(10,2), quantity NUMBER, subtotal AS (price * quantity) );

SQLite

Both VIRTUAL and STORED modes supported since 3.31.0 (2020-01-22). VIRTUAL is the default. Expression must be deterministic. Generated columns cannot be used as part of the PRIMARY KEY. Cannot be indexed unless STORED.

CREATE TABLE products ( price REAL, quantity INTEGER, subtotal REAL GENERATED ALWAYS AS (price * quantity) VIRTUAL, subtotal_s REAL GENERATED ALWAYS AS (price * quantity) STORED ); -- Shorthand (AS without GENERATED ALWAYS): CREATE TABLE products ( price REAL, quantity INTEGER, subtotal REAL AS (price * quantity) );