Generated column support was added to SQLite in version 3.31.0, which was released on 22 January 2020.

Generated columns and computed columns are the same thing. They are columns whose values are a function of other columns in the same row.

In SQLite, generated columns are created using the GENERATED ALWAYS column-constraint when creating or altering the table.

There are two types of generated column; STORED and VIRTUAL. Only VIRTUAL columns can be added when altering a table. Both types can be added when creating a table.

Example

Here’s an example to demonstrate.

CREATE TABLE Products( 
    Id INTEGER PRIMARY KEY, 
    Name TEXT,
    Qty INT,
    Price INT,
    TotalValue INT GENERATED ALWAYS AS (Qty * Price)
);

If you get the following error:

Error: near "AS": syntax error

Chances are, you need to upgrade to a later version of SQLite. Generated columns were only introduced in SQLite 3.31.0.

Now lets insert data and select it.

INSERT INTO Products VALUES 
  (1, 'Hammer', 10, 9.99),
  (2, 'Saw', 5, 11.34),
  (3, 'Wrench', 7, 37.00),
  (4, 'Chisel', 9, 23.00),
  (5, 'Bandage', 70, 120.00);

SELECT * FROM Products;

Result:

Id     Name        Qty    Price   TotalValue
-----  ----------  -----  ------  ----------
1      Hammer      10     9.99    99.9      
2      Saw         5      11.34   56.7      
3      Wrench      7      37      259       
4      Chisel      9      23      207       
5      Bandage     70     120     8400      

This is a simple example and you can certainly use more complex expressions, for example, ones that use built-in functions.

Virtual vs Stored

By default, the computed column is created as a VIRTUAL column.

You also have the option of creating a STORED column.

The value of a VIRTUAL column is computed when read, whereas the value of a STORED column is computed when the row is written.

You can explicitly use VIRTUAL or STORED in your column definition to indicate which one it should be. If you omit this, then it will be VIRTUAL.

Here’s how we can change the previous example to use a STORED column.

CREATE TABLE Products( 
    Id INTEGER PRIMARY KEY, 
    Name TEXT,
    Qty INT,
    Price INT,
    TotalValue INT GENERATED ALWAYS AS (Qty * Price) STORED
);

Regardless of whether it’s a STORED column or VIRTUAL, entering and selecting the data is exactly the same.

INSERT INTO Products VALUES 
  (1, 'Hammer', 10, 9.99),
  (2, 'Saw', 5, 11.34),
  (3, 'Wrench', 7, 37.00),
  (4, 'Chisel', 9, 23.00),
  (5, 'Bandage', 70, 120.00);

SELECT * FROM Products;

Result:

Id     Name        Qty    Price   TotalValue
-----  ----------  -----  ------  ----------
1      Hammer      10     9.99    99.9      
2      Saw         5      11.34   56.7      
3      Wrench      7      37      259       
4      Chisel      9      23      207       
5      Bandage     70     120     8400      

Updating Data in a Generated Column

You can’t update data directly in a computed column. That is, you can’t write directly to the computed column itself.

To update its data, you need to update the data in the underlying columns that are used in the computed column’s expression.

Here’s an example of updating the data used in the previous examples.

UPDATE Products 
SET Qty = 5 WHERE Id = 1;

SELECT * FROM Products;

Result:

Id     Name        Qty    Price   TotalValue
-----  ----------  -----  ------  ----------
1      Hammer      5      9.99    49.95     
2      Saw         5      11.34   56.7      
3      Wrench      7      37      259       
4      Chisel      9      23      207       
5      Bandage     70     120     8400      

In this case I reduced the number of hammers available to 5. This in turn reduced the total value of hammers in stock and the value in the generated column (TotalValue) reduced from 99.99 to 49.95.