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