SQLite Basics: Derive Values with Generated Columns

A generated column keeps a calculation beside the data it describes. Instead of repeating an expression in every query or remembering to update a second value, write the rule in the table definition. These examples require SQLite 3.31.0 or later; the tested runtime below is newer.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. The toy database disappears when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line. This is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Calculate a line total

Save as line-total.sql:

CREATE TABLE order_lines (
    id INTEGER PRIMARY KEY,
    unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0),
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    total_cents INTEGER GENERATED ALWAYS AS (unit_cents * quantity) VIRTUAL
);
INSERT INTO order_lines (id, unit_cents, quantity) VALUES (1, 250, 3);
SELECT id, total_cents FROM order_lines ORDER BY id;
UPDATE order_lines SET quantity = 4 WHERE id = 1;
SELECT id, total_cents FROM order_lines ORDER BY id;

Run:

python3 run-sql.py line-total.sql

Output:

1|750
1|1000

The insert supplies only ordinary columns. Updating quantity changes the calculated total without a second UPDATE. Naming the inserted columns makes that boundary visible and avoids accidentally trying to supply a generated value.

These small integer amounts represent cents, not binary floating-point prices. The checks reject negative inputs, but this toy schema does not validate every aspect of an order. Production code also needs realistic upper bounds so arithmetic stays within the intended numeric range.

2. Store a normalized lookup key

Save as normalized-code.sql:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    code TEXT NOT NULL,
    search_code TEXT GENERATED ALWAYS AS (upper(trim(code))) STORED
);
CREATE INDEX products_search_code ON products(search_code);
INSERT INTO products (id, code) VALUES (1, ' ab-12 ');
SELECT id, search_code FROM products WHERE search_code = 'AB-12';
UPDATE products SET code = ' cd-34 ' WHERE id = 1;
SELECT id, search_code FROM products ORDER BY id;

Run:

python3 run-sql.py normalized-code.sql

Output:

1|AB-12
1|CD-34

VIRTUAL calculates on reading; STORED calculates on writing and occupies storage. A stored value still follows changes to its inputs, so it is not a historical snapshot. Both forms can be indexed. The index here provides a lookup option; this tiny dataset is not a performance benchmark.

The example deliberately uses ASCII product codes. SQLite’s built-in upper() is not a general Unicode case-normalization solution. Define a normalization policy appropriate to your identifiers before using this pattern for real searches.

3. Add a calculated column to a table

Save as temperature-column.sql:

CREATE TABLE readings (sensor TEXT PRIMARY KEY NOT NULL, celsius REAL);
INSERT INTO readings VALUES ('desk', 20), ('outside', NULL);
ALTER TABLE readings ADD COLUMN fahrenheit REAL
GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL;
SELECT sensor, celsius, fahrenheit FROM readings ORDER BY sensor;

Run:

python3 run-sql.py temperature-column.sql

Output:

desk|20.0|68.0
outside|NULL|NULL

The existing rows immediately expose the new calculation. Missing temperature remains NULL; replacing it with zero would invent a measurement. Decide missing-data behavior deliberately rather than adding COALESCE just to eliminate blank output.

ALTER TABLE can add a VIRTUAL generated column, but not a STORED one. Generated expressions are restricted to the current row and deterministic scalar operations: a lookup into another table, a running total, or the current clock belongs elsewhere. Older SQLite versions that lack generated-column support cannot read this schema correctly.

See SQLite’s generated-column reference for storage and compatibility rules, and its core functions reference for upper(). Prefer a generated column when the value is a stable row-level rule, not a manually editable exception.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py line-total.sql normalized-code.sql temperature-column.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.