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.