SQL GENERATED

What does the SQL keyword GENERATED do?

Marks a column whose values are automatically derived by the database, either from an expression or an identity sequence.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL GENERATED: PostgreSQL 10+, MySQL 5.7+, MariaDB 10.2+, Oracle 12c+, SQL Server 2012+ (IDENTITY and computed columns), SQLite (generated columns since 3.31), DB2, Google BigQuery (GENERATED AS IDENTITY only).

SQL GENERATED Full Explanation

GENERATED is a reserved keyword defined in the SQL standard and adopted by most modern databases to declare columns whose contents are produced automatically rather than supplied by the user. Two major forms exist:1. Identity columns – The database assigns a unique sequence value to the column. Declared with "GENERATED ALWAYS AS IDENTITY" or "GENERATED BY DEFAULT AS IDENTITY". "ALWAYS" forbids user-supplied values, while "BY DEFAULT" lets applications override the sequence when needed.2. Computed (or virtual) columns – The column is calculated from an expression referencing other columns in the same table. Declared with "GENERATED ALWAYS AS (expression)". In MySQL and MariaDB you must add "VIRTUAL" or "STORED" to specify whether the value is materialised on disk.Key behaviors:- The database enforces the generation rule on INSERT and UPDATE.- Generated values participate in indexes, constraints, and queries like normal columns.- A generated column cannot usually be directly updated.Caveats:- Not all dialects support both identity and expression variants.- Expression syntax must be deterministic and free of sub-queries in many engines.- Changing a generated column after creation often requires recreating the table.

SQL GENERATED Syntax

-- Identity column (standard SQL)
column_name data_type GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [ (identity_options) ];

-- Computed column (MySQL / MariaDB)
column_name data_type GENERATED ALWAYS AS (expression) [VIRTUAL | STORED];

SQL GENERATED Parameters

ALWAYS (keyword) - Database always supplies the value; user inserts are rejected.
BY DEFAULT (keyword) - Database supplies a value unless the insert provides one.
IDENTITY_OPTIONS (clause) - Start, increment, min, max, cycle, cache settings for the sequence.
expression (SQL expression) - Deterministic formula used to compute the column.
VIRTUAL (keyword) - Value is calculated at read time and not stored on disk.
STORED (keyword) - Value is materialised and kept on disk like a normal column.

Example Queries Using SQL GENERATED

-- 1. Identity column in PostgreSQL
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id INT NOT NULL,
  total NUMERIC(10,2)
);

INSERT INTO orders (customer_id, total) VALUES (42, 99.99);
-- id is auto-assigned

-- 2. Computed column in MySQL
CREATE TABLE line_items (
  qty INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  subtotal DECIMAL(10,2) GENERATED ALWAYS AS (qty * unit_price) STORED
);

INSERT INTO line_items (qty, unit_price) VALUES (3, 19.95);
-- subtotal becomes 59.85

Expected Output Using SQL GENERATED

When inserting into orders, each row receives a unique, sequential id.
After inserting into line_items, the subtotal column contains the product of qty and unit_price without the user specifying it.

Use Cases with SQL GENERATED

Auto-incrementing primary keys without relying on proprietary AUTO_INCREMENT or SERIAL keywords.
Enforcing read-only derived metrics like subtotal, full_name, or area.
Removing duplication of logic from application code by centralising calculations in the database.
Guaranteeing uniqueness or correctness of identifier columns in multi-client systems.

Common Mistakes with SQL GENERATED

Mixing up ALWAYS and BY DEFAULT and unexpectedly getting "cannot insert into identity column" errors.
Attempting to update a generated column directly.
Using non-deterministic or disallowed functions (e.g., NOW()) in the expression in engines that forbid them.
Forgetting to mark MySQL generated columns as STORED when you need to index them (VIRTUAL columns cannot be indexed before MySQL 8.0.13).

First Introduced In

SQL:2003

Frequently Asked Questions

What databases support SQL GENERATED?

PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, SQLite, DB2, and others implement at least one form of GENERATED columns.

Can I change the value of a GENERATED column after insertion?

No. The database treats the column as read-only. To modify it, you must update the source columns or recreate the table definition.

How is GENERATED different from DEFAULT?

DEFAULT inserts a static or function-based value when the row omits the column, but applications may still override it. GENERATED ALWAYS forbids overrides and can derive its value from other columns.

Do generated columns impact performance?

Identity columns have minimal overhead. STORED computed columns add storage but speed reads. VIRTUAL columns save disk space but calculate on the fly, which can slow heavy reads.