Ticket Hash: | 908f001483982c43cdb476dfb590a1a9164e6b0a | |||
Title: | UPSERT does not work like PostgreSQL | |||
Status: | Fixed | Type: | Code_Defect | |
Severity: | Important | Priority: | Immediate | |
Subsystem: | Unknown | Resolution: | Fixed | |
Last Modified: | 2018-08-14 15:13:06 | |||
Version Found In: | 3.24.0 | |||
User Comments: | ||||
drh added on 2018-08-14 11:01:55:
The upsert in the following SQL raises an error in SQLite, as it is designed to do and as it is documented. However, in PostgreSQL, the upsert works, and leaves behind a single row with values 1,2,33,4. The behavior of SQLite should be modified so that it gets the same answer as PostgreSQL. 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); INSERT INTO t1(a,b,c,d) VALUES(1,2,33,44) ON CONFLICT(b) DO UPDATE SET c=excluded.c; SELECT * FROM t1; drh added on 2018-08-14 11:06:19: The following also works on PostgreSQL but raises a constraint error on SQLite: CREATE TABLE t1(a INT, b INT, c INT, d INT); CREATE UNIQUE INDEX t1a ON t1(a); CREATE UNIQUE INDEX t1b ON t1(b); INSERT INTO t1(a,b,c,d) VALUES(1,2,3,4); INSERT INTO t1(a,b,c,d) VALUES(1,2,33,44) ON CONFLICT(b) DO UPDATE SET c=excluded.c; SELECT * FROM t1; |