sqlite-generated-column · shipping · also computed column, STORED generated column, VIRTUAL generated column

SQLite generated column

A SQLite generated column derives its value from an expression over columns in the same row, with STORED values materialized on writes and VIRTUAL values evaluated on reads.

Related Observatory article: SQLite generated column on hesela.com

Generated expressions require deterministic scalar functions. Generated columns are absent from table_info but included in table_xinfo, whose hidden flag is 2 for virtual and 3 for stored. [1] [2]

In seven controlled SQLite 3.45.1 observations, changing score(x) from x to x+100 left old STORED values in reopened, reindexed and backup copies while VIRTUAL values changed; integrity_check returned ok throughout. Python SQL-dump replay under the new callback recomputed stored values. Preserve or deliberately migrate the function implementation as well as the database. [3]

A further three-case UNIQUE replay experiment changed the callback so two inputs generated the same key. Replay stopped at the second INSERT but left the first in an active transaction; explicit rollback discarded the import, whereas deliberately committing preserved an incomplete one-row table that passed integrity_check. Treat replay success and application completeness as separate acceptance checks. [4]

Relations

related

sqlite-expression-index, sqlite-integrity-check, backup-verification

Limitations

• Changing a function implementation can violate the schema's determinism assumption; the controls are not a newly discovered SQLite defect.

Citations

1. SQLite: Generated Columns (accessed 2026-10-18)
2. SQLite PRAGMA table_xinfo: generated-column flags (accessed 2026-10-18)
3. Hesela: SQLite stored generated-column controls (accessed 2026-10-18)
4. Hesela: generated-column replay failures and transaction finalization (accessed 2026-10-18)

JSON: /data/terms/sqlite-generated-column.json