Partial unique index not respected by insert on conflict statement - Mailing list pgsql-bugs

From Haleemur Ali
Subject Partial unique index not respected by insert on conflict statement
Date
Msg-id CAGPLuc5=qHg=Zw_4wauhgee-OQZtNfC9-ab+YE3vVbLXfk0m8g@mail.gmail.com
Whole thread Raw
List pgsql-bugs
In the following example, I'm creating a table with an integer primary key called id, a text column called color & a boolean column called `locked`.

If the `locked` column is `true`, then that row may not be updated via an insert on conflict statement. If instead, the `locked` column is `false`, then the insert statement should be able to update on conflict with the primary key.

To implement this behaviour, I define a primary key constraint on the id column, and also create a partial unique index on id where locked = false,

The insert on conflict on the partial unique index is not expected to update the row since the value of `locked` is `true`, instead, the insert statement updates the row

It seems to me that on conflict clause does not respect partial unique index as it sees that there is a total unique index on that column

steps taken to produce this error. These statements are also included in the attached file `script-hal.sql`

create table mytable(id int primary key, color text, locked bool);
create unique index on mytable (id) where locked = false;
insert into mytable values
(1, 'red', true);
insert into mytable values
(1, 'blue', true);
on conflict (id) where locked = false do update
set color = excluded.color;



The final statement shows the output
INSERT 0 1

I expected the statement to throw a duplicate key error

final state of the table:

select * from mytable;
-- outputs:
 id | color | locked
----+-------+--------
  1 | blue  | t
(1 rows)


output of `select version()`
                                                                                                     version                                                  
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.10 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.3.1 20190223 (Red Hat 8.3.1-2), 64-bit
(1 row)
Attachment

pgsql-bugs by date:

Previous
From: Michael Paquier
Date:
Subject: Re: BUG #15964: vacuumdb.c:187:10: error: use of undeclaredidentifier 'FD_SETSIZE'
Next
From: PG Bug reporting form
Date:
Subject: BUG #15974: Concact with || doesn't work, but function CONCAT () works