Virtual columns in SQlite3
- SQLite3 (from version 3.31 onwards) supports virtual columns.
- You can setup a generated column (calculated column) on a sqlite3 table.
- The column can be of two types, calculated during SELECT (VIRTUAL) or calculated during INSERT/UPDATE (STORED)
- This is a classic space (STORED) vs time (VIRTUAL) tradeoff.
- The VIRTUAL column can be added at anytime using an ALTER TABLE but a STORED column should created when the table is created.
- You cannot do a copy table equivalent using CREATE TABLE AS since it wont allow you to add new columns.