CODEWITHSUNDEEP
sqlsqlitefull-text-searchvirtual-table
rambalachandran
rambalachandran

Reputation: 2201

How to create a Full Text Search (FTS) virtual table from existing table without explicit column name?

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.

Upvotes: 1

Views: 714

Answers (0)

Related Questions