upsert unique partial index

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

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

Re: upsert unique partial index

Richard Hipp-3
On 6/5/19, ingo <[hidden email]> wrote:

> 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)

Add here:  WHERE param='_'


> DO UPDATE
> SET param = 'updated';
> --Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE
> constraint
>
> ingo
> _______________________________________________
> 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 unique partial index

ingo


On 5-6-2019 12:52, Richard Hipp wrote:
> WHERE param='_'

query executed,

thanks,

ingo
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users