Hello,
A minor observation: UPSERT makes indices to be strictly and sparingly
endowed with UNIQUEness. For example: uniqueness + partitioning:
CREATE TABLE t(num UNIQUE, cnt DEFAULT 1);
CREATE UNIQUE INDEX idx_neg ON t(num) WHERE num < 0;
CREATE UNIQUE INDEX idx_pos ON t(num) WHERE num >= 0;
Now ``INSERT INTO t(num) VALUES(10) ON CONFLICT(num) DO UPDATE SET
cnt=cnt+1;'' fails due to a fact that ``idx_pos'' will not pass. It is a
frequent and common habit to put ``UNIQUE'' everywhere an entity is
unique. From SQLite 3.24, above--mentioned, partial indices (and
everything what is not strictly designed to enforce an uniqueness)
should be non-UNIQUE ones. IMHO, this fact is worth of recalling in the
doc of UPSERT clause as the fact can become a source of a confusion.
-- best regards
Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
