Simplified test case:
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT);
CREATE UNIQUE INDEX t1b ON t1(b);
INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4);
SELECT * FROM t1;
INSERT INTO t1(a,b,c,d) VALUES(1,2,33,44)
ON CONFLICT(b) DO UPDATE SET c=excluded.c;
The second INSERT fails raises a conflict on the primary key in
SQLite, but it works in PostgreSQL, leaving behind a single row with
values 1,2,33,4. SQLite actually does what I designed it to do, and
what it is documented to do, because that is the way that I understood
PostgreSQL would work, based on my reading of the PostgreSQL
documentation and on test queries. However, I must have missed
something. I will strive to bring the behavior of SQLite into
alignment with PostgreSQL.
On 8/13/18, Jordan Owens <jkow...@gmail.com> wrote:
> 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
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
