I'm having trouble executing an INSERT ... ON CONFLICT with a partial
index. It works fine in the SQLite shell, but it fails when I express the
conflict constraint using a parameterized query.
For example:
CREATE TABLE ukvp ("id" integer primary key, "key" text not null, "value"
int not null, "extra" int not null);
CREATE UNIQUE INDEX ukvp_partial ON ukvp (key, value) WHERE extra > 1;
INSERT INTO ukvp (key, value, extra) VALUES ('k1', 1, 1);
INSERT INTO ukvp (key, value, extra) VALUES ('k2', 2, 2);
INSERT INTO ukvp (key, value, extra) VALUES ('k1', 1, 2), ('k2', 2, 3)
ON CONFLICT (key, value) WHERE extra > 1
DO UPDATE set extra=EXCLUDED.extra;
This works OK in the SQLite CLI, but when attempting to execute such a
query using a prepared query I get the exception: "ON CONFLICT clause does
not match any PRIMARY KEY or UNIQUE constraint". I believe it is because
the "extra > 1" must be exactly and literal -- the "1" cannot be a
parameter in the query.
For example, using a Python SQLite library, this fails with the above error
message.
sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, ?), (?,
?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > ?) DO UPDATE SET
"extra" = EXCLUDED."extra"''
db.execute(sql, ('k1', 1, 2, 'k2', 2, 3, 1))
But the following works:
sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, ?), (?,
?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > 1) DO UPDATE SET
"extra" = EXCLUDED."extra"''
db.execute(sql, ('k1', 1, 2, 'k2', 2, 3))
Is this a bug?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
