Generated columns in SQLite

Sometimes an SQL query field is calculated based on other table columns. Imagine a table with income and tax_rate columns:

┌────────┬──────────┐
│ income │ tax_rate │
├────────┼──────────┤
│ 70     │ 0.22     │
│ 84     │ 0.22     │
│ 90     │ 0.24     │
└────────┴──────────┘

You can calculate the annual tax:

select
  id,
  income * tax_rate as tax
from people;

In order not to repeat these calculations everywhere, it is convenient to create a virtual generated column:

alter table people
add column tax real as (
  income * tax_rate
);

After that, the column can be used in queries in the same way as regular columns:

select id, tax
from people;

Virtual columns are not stored in the database and are calculated on the fly. But you can build an index on them to speed up data retrieval.

Note. Strictly speaking, SQLite has virtual generated columns and stored ones. The latter are stored on disk, but it is impossible to create them via alter table, so people mostly use virtual ones.

In general, the syntax is as follows:

alter table TABLE
add column COLUMN TYPE as (EXPRESSION);

Generated column expression can include any table columns, but not other tables or subquery results. It's for the best: for more complex combinations, there are views and temp tables. Let's talk about them some other time.

Documentation • Playground

──

P.S. Interested in mastering advanced SQL? Check out my book — SQL Window Functions Explained

 Subscribe to keep up with new posts.