Generated columns in SQL

A column whose value is a formula cannot disagree with the columns it reads.

CREATE TABLE line_items (
    id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL,
    unit_price REAL NOT NULL,
    total REAL GENERATED ALWAYS AS (quantity * unit_price) STORED,
    sku_upper TEXT GENERATED ALWAYS AS (UPPER(sku)) VIRTUAL,
    sku TEXT NOT NULL
);

INSERT INTO line_items (quantity, unit_price, sku)
VALUES (3, 19.99, 'ts-001');

How it works

1. VIRTUAL computes on read and costs no storage; STORED writes it down.
2. You never insert into a generated column — the engine fills it.
3. You can index a stored generated column like any other.

Keywords and builtins used here

• AS
• CREATE
• GENERATED
• INSERT
• INTEGER
• INTO
• KEY
• NOT
• NULL
• PRIMARY
• REAL
• TABLE
• TEXT
• UPPER
• VALUES

The run, in numbers

Lines

11

Characters to type

324

Tokens

71

Three-star pace

85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 50 seconds.

Type this snippet

Step 5 of 5 in Constraints, step 5 of 16 in Schema & constraints.

← Previous Next →