1

This question describes how to create a FTS table (tabel2) from existing table (table1) by explicitly providing column names which boils down to:

CREATE VIRTUAL TABLE table2 USING FTS5(col1, col2);
INSERT INTO table2 SELECT * FROM table1;

However in my case I have a table with hundreds of columns that makes it hard to explicitly name them. I tried to use select with pragma_table_info :

CREATE VIRTUAL TABLE table2 USING FTS5(SELECT name FROM pragma_table_info('table1');

However this throws the error :

[SQLITE_ERROR] SQL error or missing database (parse error in "SELECT name FROM pragma_table_info('table1')")

How can I automatically create a virtual FTS table from existing table without explicitly mentioning column names? I can get the CREATE TABLE command from DBeaver. However this command contains types and constraints which are an ERROR in FTS5.

user4157124
  • 2,809
  • 13
  • 27
  • 42
rambalachandran
  • 2,091
  • 2
  • 19
  • 34
  • Construct and execute ddl statement in Python. There is no dynamic SQL in SQLite. You can construct a ddl statement using SQL, but you will have to copy it from SQLite output and paste it to SQL shell for execution. – PChemGuy Nov 02 '22 at 06:18

0 Answers0