SQL Reference ยท Data Definition
SQL generated columns
Reference for SQL generated (computed) columns: VIRTUAL and STORED columns, indexing them, limits, and syntax in SQLite, PostgreSQL, MySQL and SQL Server.
A generated column is a column whose value the database computes from other columns in the same row. You never write to it; it is always consistent with its inputs. It is useful for things you would otherwise compute in every query (a full name, a total) or want to index (the lowercase form of an email).
There are two storage modes. A virtual column is calculated when read and takes no space. A stored column is calculated when the row is written and kept on disk, so reads are fast but writes cost a little more.
CREATE TABLE line_items ( quantity INTEGER NOT NULL, unit_price REAL NOT NULL, line_total REAL GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
column_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED]
| Part | Description |
|---|---|
expression |
Must be deterministic and may use only columns of the same row (no subqueries, no random(), no other tables). |
VIRTUAL |
Computed on read. Default in SQLite and MySQL. |
STORED |
Computed on write and saved. The only choice in PostgreSQL. |
GENERATED ALWAYS |
Optional noise word in SQLite and MySQL; required in PostgreSQL. |
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | 3.31+. VIRTUAL (default) or STORED. Can be indexed. Added later with ALTER TABLE ADD COLUMN only if VIRTUAL. |
| PostgreSQL | 12+: GENERATED ALWAYS AS (expr) STORED. Virtual columns arrived only in PostgreSQL 18. |
| MySQL | 5.7+: VIRTUAL or STORED; can be indexed. |
| SQL Server | Computed columns: full_name AS (first + ' ' + last), optionally PERSISTED. |
| Oracle | VIRTUAL columns: col AS (expr) (11g+). |
-- PostgreSQL CREATE TABLE people ( first_name text, last_name text, full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED ); -- SQL Server ALTER TABLE people ADD full_name AS (first_name + ' ' + last_name) PERSISTED;
Usage notes
- Read-only. Inserting or updating a generated column is an error (in PostgreSQL, only
DEFAULTis accepted). - Expression changes usually mean dropping and recreating the column; storing it makes that a table rewrite.
SELECT *includes generated columns in most databases; in SQLite virtual ones show, butINSERTwithout a column list must skip them.- Indexing a generated column is the portable way to get an "index on an expression" in databases that lack one.
- Alternatives: compute it in a view, or keep a normal column maintained by a trigger (more flexible, more work).