SQLite

View Ticket
Login
Plaintext
2018-08-14
15:13 • Fixed ticket [908f0014]: UPSERT does not work like PostgreSQL plus 3 other changes (artifact: d55756a6 user: drh)
15:12
Fix UPSERT so that it checks the target-constraint first and fires the DO UPDATE if that constraint is violated regardless of whether or not other constraints are in violation. This aligns SQLite behavior with what PostgreSQL does. Fix for ticket [908f001483982c43cdb476dfb590a1a]. (check-in: 529fb55e user: drh tags: trunk)
11:06 • Ticket [908f0014] UPSERT does not work like PostgreSQL status still Open with 6 other changes (artifact: c781e55d user: drh)
11:01 • New ticket [908f0014]. (artifact: 3f51aa1a user: drh)
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;