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

Reply via email to