You can add a generated column to an existing table in SQLite by using the ALTER TABLE statement.

SQLite’s implementation of the ALTER TABLE statement is very limited, but it does allow you to add a column – including generated columns.

Generated columns (also known as “computed columns”) are columns that get their value from an expression that computes values from other columns.

Example

Here’s a simple example to demonstrate.

Original Table

Let’s first create a table without a generated column and insert some data:

CREATE TABLE Person( 
    Id INTEGER PRIMARY KEY, 
    FirstName,
    LastName
);

INSERT INTO Person VALUES 
    ( 1, 'Barney', 'Rubble' ),
    ( 2, 'Peter', 'Griffin' ),
    ( 3, 'Fritz', 'The Cat' );

SELECT * FROM Person;

Result:

Id          FirstName   LastName  
----------  ----------  ----------
1           Barney      Rubble    
2           Peter       Griffin   
3           Fritz       The Cat   

Add the Generated Column

Now let’s add a generated column that table.

ALTER TABLE Person ADD COLUMN 
  FullName GENERATED ALWAYS AS (FirstName || ' ' || LastName);

SELECT * FROM Person;

Result:

Id          FirstName   LastName    FullName     
----------  ----------  ----------  -------------
1           Barney      Rubble      Barney Rubble
2           Peter       Griffin     Peter Griffin
3           Fritz       The Cat     Fritz The Cat

A generated column called FullName was added. This column concatenates the FirstName column with the LastName column, and therefore anyone who queries this table no longer needs to perform that concatenation themselves in their query.

Updating Data

You can’t update data in the generated column directly. You need to update the data in the underlying columns that contribute to its value.

So if we wanted to update Barney Rubble to Betty Rubble, we would need to update the FirstName column.

UPDATE Person 
SET FirstName = 'Betty'
WHERE Id = 1;

SELECT * FROM Person;

Result:

Id          FirstName   LastName    FullName       
----------  ----------  ----------  ---------------
1           Betty       Rubble      Betty Rubble   
2           Peter       Griffin     Peter Griffin  
3           Fritz       The Cat     Fritz The Cat