Store a Computed Value Automatically With Generated Columns

Define a column whose value is always derived from an expression over other columns — the database recomputes or stores it on every write so queries never need to repeat the formula.

Created Apr 26, 2026 Last updated Apr 26, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Auto-compute a line-item total from quantity and unit price

Each row is inserted with only id, qty, and unit_price. The engine computes total_price = qty * unit_price automatically: row 1 gets 3 × 10 = 30, row 2 gets 5 × 8 = 40, row 3 gets 2 × 25 = 50. The STORED keyword (or PERSISTED in SQL Server) writes this computed value to disk so it can be indexed and read without re-evaluating the expression. Attempting to set total_price explicitly in an INSERT or UPDATE would raise an error — the column is always owned by the engine. SQL Server omits the data type in the column definition because the engine infers it from the expression; all other engines require the type to be declared before GENERATED ALWAYS AS.

MySQL MariaDB PostgreSQL SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE order_items (
  id INT,
  qty INT,
  unit_price INT,
  total_price INT GENERATED ALWAYS AS (qty * unit_price) STORED
);

INSERT INTO
  order_items (id, qty, unit_price)
VALUES
  (1, 3, 10),
  (2, 5, 8),
  (3, 2, 25);

Validated query

SQL

SELECT
  id,
  qty,
  unit_price,
  total_price
FROM
  order_items
ORDER BY
  id;

Expected result

id	qty	unit_price	total_price
1	3	10	30
2	5	8	40
3	2	25	50

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE order_items (
  id INT,
  qty INT,
  unit_price INT,
  total_price AS qty * unit_price PERSISTED
);

INSERT INTO
  order_items (id, qty, unit_price)
VALUES
  (1, 3, 10),
  (2, 5, 8),
  (3, 2, 25);

Validated query

SQL

SELECT
  id,
  qty,
  unit_price,
  total_price
FROM
  order_items
ORDER BY
  id;

Expected result

id	qty	unit_price	total_price
1	3	10	30
2	5	8	40
3	2	25	50

MySQL, MariaDB, PostgreSQL, and SQLite use GENERATED ALWAYS AS (expr) STORED. SQL Server uses col AS expr PERSISTED with no type declaration. Results are identical.

Useful when

Where this command helps.

automatically computing a line-item total from quantity and unit price without requiring application logic on every write
storing a normalized or lowercased version of a column for indexed lookups without duplicating write logic

Explanation

What the command is doing.

A generated column derives its value from a deterministic expression over other columns in the same row. There are two variants: stored (also called persisted) columns write the computed value to disk on every insert or update, making reads fast at the cost of extra storage; virtual columns recompute the expression at read time with no storage overhead. MySQL 5.7+ and MariaDB 5.2+ support both VIRTUAL and STORED variants using GENERATED ALWAYS AS (expr). PostgreSQL 12+ supports only STORED; virtual generated columns are not yet available. SQLite 3.31+ supports both variants. SQL Server uses a different syntax — col AS expr — and adds PERSISTED to request disk storage. Generated columns cannot reference other generated columns in most engines, and expressions must be deterministic (no RAND(), NOW(), or user-defined non-deterministic functions). The column cannot be set explicitly in an INSERT or UPDATE; the engine always computes it.

Store a Computed Value Automatically With Generated Columns

Auto-compute a line-item total from quantity and unit price

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.