Partial unique index not respected by insert on conflict statement

From: Haleemur Ali <haleemur(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Partial unique index not respected by insert on conflict statement
Date: 2019-08-23 05:04:59
Message-ID: CAGPLuc5=qHg=Zw_4wauhgee-OQZtNfC9-ab+YE3vVbLXfk0m8g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 Content-Type Size
script-hal.sql application/sql 295 bytes

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-08-23 11:16:19 BUG #15974: Concact with || doesn't work, but function CONCAT () works
Previous Message Michael Paquier 2019-08-23 01:59:21 Re: BUG #15964: vacuumdb.c:187:10: error: use of undeclared identifier 'FD_SETSIZE'