Hmm, I think I've found the solution: INSERT INTO search (rowid, question, answer, altAnswers, comments, authors, sources) SELECT id, question, answer, altAnswers, comments, authors, sources FROM questions WHERE obsolete IS NULL;
On Tue, Oct 31, 2017 at 1:02 PM Eugene Mirotin <emiro...@gmail.com> wrote: > Hi, > > I have a table questions with lots of columns. Out of them 6 columns > represent text content and the rest are technical (FKs, update timestamp, > etc) > > I need to build the FTS5 search index for this table to only index the > content columns (that 6). > The DB is only built once on the server and later used as read-only in the > client app (which downloads it from the server) > It works fine unless I try to use the external content table feature > (which I need to reduce the table size and also to be able to make JOIN > queries to get the meta info from the original table for each matching row. > > The original table (called questions) has the PK column called id. This is > the column I want to use as rowid for the search index and for joining the > tables. > > Here's how I'm trying to create and populate the search table: > > DROP TABLE IF EXISTS search; > CREATE VIRTUAL TABLE search USING fts5(question, answer, altAnswers, > comments, authors, sources, tokenize = "snowball russian english > unicode61", content=questions, content_rowid=id); > INSERT INTO search SELECT id, question, answer, altAnswers, comments, > authors, sources FROM questions WHERE obsolete IS NULL; > > At this point INSERT fails because I'm trying to insert 7 columns into the > table which has 6. > > I also tried creating id as UNINDEXED column in the search table and that > passed but the search results were silly, matching completely unrelated > tokens. > > So what is the proper way to insert all rows from questions into the > search table? > > > Thanks in advance > > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
