Create a partial index for active rows

By Batu · English technical notes

A partial index contains entries for only the rows whose index WHERE expression is true. Here, CREATE INDEX ... ON task(label) WHERE active = 1 records labels for active tasks while omitting inactive ones. The SQLite partial-index documentation defines this behavior and notes that rows with a false or NULL predicate result are omitted.

The in-memory fixture has one inactive task (draft) and two active tasks (ship and test). After the index is created, the code reads its schema SQL from sqlite_master and asserts that the index definition retains WHERE active = 1. It separately selects active labels in lexical order and asserts that the concrete result is exactly ['ship', 'test']. The resulting output, ship, test, makes the selected rows clear without relying on planner-output formatting.

The query result does not prove that SQLite used the index, and the schema assertion does not expose physical index entries. If plan choice matters, inspect it separately with EXPLAIN QUERY PLAN while remembering that its output is not a stable API. A partial-index predicate cannot contain bound parameters, subqueries, references to other tables, or non-deterministic functions. It also helps only when SQLite can establish that the query’s condition implies the predicate. Partial indexes require SQLite 3.8.0 or later; this example uses no newer Python-specific API.

AI assistance disclosure: Batu Lab Notes used AI assistance to draft this article; verify behavior in the Python and SQLite versions you deploy.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE task (id INTEGER PRIMARY KEY, label TEXT, active INTEGER NOT NULL)")
con.executemany(
    "INSERT INTO task VALUES (?, ?, ?)",
    [(1, "draft", 0), (2, "ship", 1), (3, "test", 1)],
)
con.execute("CREATE INDEX idx_active_task_label ON task(label) WHERE active = 1")

sql = con.execute(
    "SELECT sql FROM sqlite_master "
    "WHERE type = 'index' AND name = 'idx_active_task_label'"
).fetchone()[0]
assert "WHERE active = 1" in sql

labels = [row[0] for row in con.execute(
    "SELECT label FROM task WHERE active = 1 ORDER BY label"
)]
assert labels == ["ship", "test"]

print(", ".join(labels))
con.close()

ship, test