Hello,
I believe I have discovered a bug with UPSERT when a table has a unique
partial index. I'm getting a constraint exception instead of the database
updating the record.
SQLite v3.24.0
*Error message:*
SQLite3::ConstraintException: UNIQUE constraint failed: alarms.id:
INSERT INTO
"alarms"("id","device_id","alarm_type","status","metadata","created_at","updated_at")
VALUES (99,17,1,2,'bar','2018-08-13 21:15:38.394975','2018-08-13
21:15:38.394975')
ON CONFLICT (device_id, alarm_type)
WHERE status <> 0
DO UPDATE SET "status"=EXCLUDED."status","updated_at"=EXCLUDED."updated_at"
*Table definition:*
CREATE TABLE "alarms" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"device_id" integer NOT NULL, "alarm_type" integer NOT NULL, "status"
integer NOT NULL, "metadata" text, "secret_key" blob, "created_at"
datetime, "updated_at" datetime)
CREATE UNIQUE INDEX "index_alarms_on_device_id_and_alarm_type" ON "alarms"
("device_id", "alarm_type") WHERE status <> 0
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
