SQLite Forum

Generated Columns
Login

Generated Columns

(1) By anonymous on 2020-09-09 16:34:52 [link] [source]

New to SQL. I want to create a Generated or Computed column in one of my database tables. I tried with my own data, and it is not working, so I have gone back to the example in the SQLite documentation:

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

And I keep getting the same error every time:

[12:27:47] Error while executing SQL query on database 'Tester2': near "AS": syntax error


I have SQLite 3.32.3 and am using SQLiteStudio 3.2.1. Other than the need to have SQLite > 3.31.0 for creating GENERATED COLUMNS, not sure what the issue is. Any help would be greatly appreciated!!

Thanks

(2) By Larry Brasfield (LarryBrasfield) on 2020-09-09 17:01:39 in reply to 1 [link] [source]

What does select sqlite_version(); say? Your DDL runs fine with SQLite 3.32.2 and 3.33.0 .

(3) By David Empson (dempson) on 2020-09-10 00:10:13 in reply to 1 [source]

Looking at their change log, SQLiteStudio 3.2.0 updated its embedded copy of SQLite to version 3.24.0, and there is no mention of a newer version of SQLite being included in SQLiteStudio 3.2.1. The SQLiteStudio news page says SQLIte 3.30 or 3.31 will be included in the next version, SQLiteStudio 3.2.2.

The most likely explanation for your problem is that SQLiteStudio 3.2.1 is unable to use the generated columns feature because it uses an embedded copy of SQLite which is too old. If you answer Larry's question (running the SELECT sqlite_version(); statement inside SQLiteStudio), this will confirm whether my explanation is correct.

Most utilities that let you directly manipulate SQLite databases contain their own linked copy of a specific version of the SQLite engine. They don't use any external SQLite you might have (such as a DLL).

If you want a utility able to work on a database that has generated columns, you will either need to wait for a future version of SQLiteStudio, or use a different utility which already includes SQLite 3.31.0 or later (such as the latest SQLite command line tool).

(4) By anonymous on 2020-09-10 13:08:17 in reply to 2 [link] [source]

Thanks for the reply Larry!

I found the issue. I am running SQLite through SQLiteStudio, and SQLiteStudio comes with SQlite version 3.24.0, and it cannot be swapped out. Too bad! Looks like the next release of SQLiteStudio is coming soon, and will use the updated version of SQLite. I'll have to wait for that release to use Generated Columns through Studio.

Thanks again!

(5) By anonymous on 2020-09-10 13:12:43 in reply to 3 [link] [source]

Hi David,

Thank you for this. I figured this out yesterday afternoon after the post from Larry. You described it much more elegantly than I did!! I built the database in Studio, meaning the DB is 3.24.0. I will either wait for the new Studio which will come soon hopefully, or get my DB up to the newest version using the command line tool. This is a side-project right now, I have some time before this needs to be ready.

Thanks David.

(6) By anonymous on 2020-09-18 08:41:34 in reply to 4 [link] [source]

It can very well be swapped out, but it won't help. You can use SQLite 3.33 and it will work, but when trying to use a database which contains generated columns, SQLite studio will crash.