Generated columns in SQLite
source link: https://antonz.org/generated-columns/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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.
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.
Follow @ohmypy on Twitter to keep up with new posts 🚀
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK