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)