Hi! Haven't been able to find a solution reading sqlite-users mailing list, http://www.sqlite.org/, nor googling at large.
I need to insert rows with a unique column combination into a fts3 virtual table. http://www.sqlite.org/fts3.html states: "This is pure syntactic sugar, the supplied typenames are not used by FTS or the SQLite core for any purpose. The same applies to any constraints specified along with an FTS column name - they are parsed but not used or recorded by the system in any way." Given CREATE VIRTUAL TABLE fts3_table USING FTS3 (a,b,c,UNIQUE(a,b,c)); to INSERT OR IGNORE INTO fts3_table VALUES(a, b, c); would never IGNORE based on a uniqueness constraint, right? This is what my testing shows. The row gets inserted no matter whether it already exists in the fts3_table. I found the following solution to work (based on some google results), but performance is miserable for 1000 such inserts with varying values, even though perfomed inside a single transaction: INSERT INTO fts3_table (a,b,c) SELECT 'an A','a B','a C' WHERE NOT EXISTS (SELECT DISTINCT a,b,c FROM fts3_table WHERE a='an A' AND b='a B' AND c='a C'); Is there a more efficient way to insert unique rows (with constraint UNIQUE(a,b,c)) into an fts3 virtual table? I am using the SQLite 3.5.9 that comes with android-2.1-update1. Thanks! -- Adrian Aichner mailto:adrian.aich...@gmail.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
