How to use SQLite's generated columns?

SQLite's Generated Columns provide a way to automatically calculate column values:

1. Generated Column Concept

   • Generated column values are automatically calculated by expressions
   • Cannot directly insert or update generated column values
   • Divided into two types: STORED and VIRTUAL

2. Generated Column Types

   • STORED: Calculation results are stored on disk, takes up space but queries are fast
   • VIRTUAL: Calculated each time during query, takes up no space but queries are slow

3. Creating Generated Columns

   sql
   -- STORED generated column
   CREATE TABLE products (
       id INTEGER PRIMARY KEY,
       name TEXT,
       price REAL,
       quantity INTEGER,
       total REAL GENERATED ALWAYS AS (price * quantity) STORED
   );
   
   -- VIRTUAL generated column
   CREATE TABLE users (
       id INTEGER PRIMARY KEY,
       first_name TEXT,
       last_name TEXT,
       full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL
   );

4. Generated Column Constraints

   • Expression must be deterministic, cannot contain random functions
   • Cannot reference other generated columns
   • Cannot reference other rows in the same table
   • Cannot contain subqueries
   • Cannot use non-deterministic functions (like random())

5. Advantages of Using Generated Columns

   • Data Consistency: Automatically keep data synchronized
   • Reduce Redundancy: Avoid manual maintenance of calculated fields
   • Performance Optimization: STORED columns can be indexed
   • Code Simplification: Reduce application layer calculation logic

6. Creating Indexes for Generated Columns

   sql
   CREATE TABLE orders (
       id INTEGER PRIMARY KEY,
       order_date TEXT,
       status TEXT,
       year INTEGER GENERATED ALWAYS AS (CAST(strftime('%Y', order_date) AS INTEGER)) STORED
   );
   
   -- Create index for generated column
   CREATE INDEX idx_year ON orders(year);

7. Modifying Generated Columns SQLite does not support directly modifying generated columns, need to:

   sql
   -- Create new table
   CREATE TABLE products_new (
       id INTEGER PRIMARY KEY,
       name TEXT,
       price REAL,
       quantity INTEGER,
       total REAL GENERATED ALWAYS AS (price * quantity * 1.1) STORED
   );
   
   -- Migrate data
   INSERT INTO products_new (id, name, price, quantity)
   SELECT id, name, price, quantity FROM products;
   
   -- Drop old table, rename new table
   DROP TABLE products;
   ALTER TABLE products_new RENAME TO products;

8. Generated Column Use Cases

   • Calculated fields (total, average, etc.)
   • Data transformation (date format conversion, type conversion)
   • Data extraction (extract fields from JSON)
   • Data validation (check conditions)
   • Derived data (calculated based on other columns)

9. Performance Considerations

   • STORED columns: Take up storage space, fast queries, slow writes
   • VIRTUAL columns: No storage space, slow queries, fast writes
   • Choose appropriate type based on query frequency
   • Create indexes for frequently queried STORED columns

10. Combining with Other Features

    • Combine with JSON extension: Extract JSON fields
    • Combine with full-text search: Generate search text
    • Combine with constraints: Validate data integrity

Generated columns are a powerful tool for SQLite to provide automated data maintenance.