Skip to content
Tutorials Center
My learning

Topics

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.

SQL
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

SQL Virtual and stored generated columns

Press Run (or Ctrl + Enter) to see the result here.

SQL Index a generated column; cannot write to it (fails on purpose)

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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+).
SQL
-- 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 DEFAULT is 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, but INSERT without 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).