[sqlite] upsert unique partial index

[ingo]

First it tells me an unique constraint failed and then it can't find it?

CREATE TABLE testupsert (
       id INTEGER NOT NULL,
       param TEXT NOT NULL DEFAULT '_',
       sometxt TEXT
);

CREATE UNIQUE INDEX up
ON testupsert (id, param)
WHERE param = '_';

INSERT INTO testupsert (id, sometxt)
VALUES (1,'1'), (2,'2');

INSERT INTO testupsert (id, sometxt)
VALUES (1,'test')
--as expected
--Error: UNIQUE constraint failed: testupsert.id, testupsert.param

INSERT INTO testupsert (id, sometxt)
VALUES (1,'test')
ON CONFLICT (id, param)
DO UPDATE
SET param = 'updated';
--Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE
constraint

ingo
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users