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.
SQLite3::ConstraintException: UNIQUE constraint failed: alarms.id:
VALUES (99,17,1,2,'bar','2018-08-13 21:15:38.394975','2018-08-13
ON CONFLICT (device_id, alarm_type)
WHERE status <> 0
DO UPDATE SET "status"=EXCLUDED."status","updated_at"=EXCLUDED."updated_at"
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 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/14/18, Richard Hipp <[hidden email]> wrote:
> > I will strive to bring the behavior of SQLite into
> > alignment with PostgreSQL.
> Now fixed on trunk
> D. Richard Hipp
> [hidden email] >