If you’re trying to add a generated column in SQLite, and you’re getting the “cannot add a STORED column” error, it’s probably because you’re trying to add a stored column.

In SQLite, we can only add virtual columns to existing tables.

Example of Error

Here’s an example of code that produces the error:

ALTER TABLE Products ADD COLUMN
    TotalValueStored INT GENERATED ALWAYS AS (Qty * Price) STORED;

Result:

Runtime error: cannot add a STORED column

When I first got that error, I thought maybe it’s a bug. After all, I tried changing STORED to VIRTUAL and it worked. Given SQLite supports both stored and virtual generated columns, surely this should work?

But upon further investigation, it seems not. According to the SQLite documentation for generated columns:

Only VIRTUAL columns can be added using ALTER TABLE.

So it looks like we can’t add stored generated columns to an existing table.

Solution

So it seems that the solution is to either:

• Add a VIRTUAL column instead OR
• Drop the table and create it again, this time with the STORED generated column OR
• Create a new table altogether with the STORED generated column
• Not bother with a generated column and use some other solution

Just to be clear, if we were to use option 1 (i.e. add a VIRTUAL column instead), then we could simply change the previous code to the following:

ALTER TABLE Products ADD COLUMN
    TotalValueStored INT GENERATED ALWAYS AS (Qty * Price) VIRTUAL;

Alternatively, we can omit the STORED and VIRTUAL keywords altogether. The default is VIRTUAL, so when we do this, a VIRTUAL generated column is added:

ALTER TABLE Products ADD COLUMN
    TotalValueStored INT GENERATED ALWAYS AS (Qty * Price);