5 ms·
Note that you don't actually need the generated column either, SQLite supports indexes on expressions, so you can do, for example, CREATE INDEX subjectidx ON
by Hakkin 1y ago
Note that you don't actually need the generated column either, SQLite supports indexes on expressions, so you can do, for example,
CREATE INDEX subjectidx ON messages(json_extract(headers, '$.Subject'))
and it will use this index anywhere you reference that expression.
I find it useful to create indexes like this, then create VIEWs using these expressions instead of ALTER'ing the main table with generated columns.
- pkhuong 1y agoAnd since view and indexes don't change the data, you can use tools like https://github.com/fsaintjacques/recordlite https://github.com/fsaintjacques/recordlite to automate schema management.
- snthpy 1y agoThis is cool! I quite like this.
- randito 1y agoWhat a great timely tip. Was just looking for good direction on how to do this. Thanks!