SQLite Forum

select * from t; -- but don't want virtual columns
Login
```
SQLite version 3.34.0 2020-09-11 22:07:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table ts(x, last_update as (datetime()) stored);
sqlite> insert into ts values (1, datetime());
Error: table ts has 1 columns but 2 values were supplied
sqlite> insert into ts values (1);
sqlite> insert into ts values (2);
sqlite> select * from ts;
┌───┬───────────────────────────┐
│ x │        last_update        │
├───┼───────────────────────────┤
│ 1 │ 2020-09-14 09:09:35.532 Z │
│ 2 │ 2020-09-14 09:09:50.556 Z │
└───┴───────────────────────────┘
sqlite> update ts set last_update=datetime();
Error: cannot UPDATE generated column "last_update"
sqlite> update ts set x=3 where x=1;
sqlite> select * from ts;
┌───┬───────────────────────────┐
│ x │        last_update        │
├───┼───────────────────────────┤
│ 3 │ 2020-09-14 09:10:10.539 Z │
│ 2 │ 2020-09-14 09:09:50.556 Z │
└───┴───────────────────────────┘
sqlite> create table tv(x, last_update as (datetime()) virtual);
sqlite> insert into tv values (1, datetime());
Error: table tv has 1 columns but 2 values were supplied
sqlite> insert into tv values (1);
sqlite> insert into tv values (2);
sqlite> select * from tv;
┌───┬───────────────────────────┐
│ x │        last_update        │
├───┼───────────────────────────┤
│ 1 │ 2020-09-14 09:11:32.773 Z │
│ 2 │ 2020-09-14 09:11:32.773 Z │
└───┴───────────────────────────┘
sqlite> update tv set x=3 where x=1;
sqlite> select * from tv;
┌───┬───────────────────────────┐
│ x │        last_update        │
├───┼───────────────────────────┤
│ 3 │ 2020-09-14 09:11:55.549 Z │
│ 2 │ 2020-09-14 09:11:55.549 Z │
└───┴───────────────────────────┘
sqlite> update tv set last_update=datetime();
Error: cannot UPDATE generated column "last_update"
sqlite>
```

See the difference?  

When a GENERATED COLUMN is STORED the value is stored in the field of the record when when the record is "written" (inserted or updated) -- it is basically a "default" for which you cannot specify a value -- and when the record is retrieved the stored value is returned.  It takes up storage space in the record payload.

When a GENERATED COLUMN is VIRTUAL the value is not stored in the table but is rather generated VIRTUALLY (computed) when the record is retrieved.  It does not take up space in the record payload.

So a STORED virtual column is a column that is computed and then stored as part of the record, just like a default.  Except, unlike a default, you cannot override it with user provided data.