Generated Columns

~10 min · generated-columns, schema

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Derived values, automatically

A generated column is computed from an expression over other columns of the same row. Two flavors:

VIRTUAL — computed on read; takes no extra storage. Default if you don't say.
STORED — computed on write and stored in the row; faster to read, takes space.

Use them for derived values you query often (e.g., the domain part of an email, the length of a content blob, a JSON-extracted field). Generated columns can be indexed — including STORED ones for very fast lookups.

Tip: STORED generated columns + indexes are the cleanest way to materialize a derived value for query performance without writing an UPDATE trigger. The schema enforces the derivation; you can't accidentally store an inconsistent value.

Code

Generated columns — VIRTUAL and STORED·sql

CREATE TABLE users (
  id        INTEGER PRIMARY KEY,
  email     TEXT NOT NULL UNIQUE,
  email_lc  TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL,
  domain    TEXT GENERATED ALWAYS AS (substr(email, instr(email,'@')+1)) STORED
) STRICT;

-- Index the stored generated column
CREATE INDEX idx_users_domain ON users(domain);

INSERT INTO users(email) VALUES ('Alice@Gmail.com'), ('bob@example.com');

SELECT email, email_lc, domain FROM users;
-- Alice@Gmail.com | alice@gmail.com | Gmail.com
-- bob@example.com | bob@example.com | example.com

External links

Generated Columns

Exercise

Add three generated columns to a table you have: a lower-cased version of a TEXT column, a JSON-extracted scalar from a JSON column, and a length-of-content column. Index one of them. Confirm queries on the indexed column use the index via EXPLAIN QUERY PLAN. Decide which should be VIRTUAL and which STORED.

Generated Columns

Derived values, automatically

Code

External links

Exercise

Progress

Comments 0