UPSERT fails for unique partial index

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

UPSERT fails for unique partial index

Jordan Owens
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT fails for unique partial index

Richard Hipp-3
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT fails for unique partial index

Petite Abeille-2


> On Aug 14, 2018, at 12:57 PM, Richard Hipp <[hidden email]> wrote:
>
> I will strive to bring the behavior of SQLite into alignment with PostgreSQL.

If only all this energy was spent on a proper MERGE clause instead… sigh… :D

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT fails for unique partial index

Richard Hipp-3
In reply to this post by Richard Hipp-3
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]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPSERT fails for unique partial index

Jordan Owens
Awesome, thanks!

On Tue, Aug 14, 2018 at 11:16 AM Richard Hipp <[hidden email]> wrote:

> 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]
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users