By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table table1 (
id serial primary key,
user_id text,
well_id text,
deleted int
);
-- the index below is unique only for the records which have deleted = 0
create unique index on table1 (user_id, well_id) where deleted = 0;
insert into table1 (user_id, well_id, deleted) values ('user1', 'well1', 0);
insert into table1 (user_id, well_id, deleted) values ('user1', 'well1', 1);
insert into table1 (user_id, well_id, deleted) values ('user1', 'well1', 1);
insert into table1 (user_id, well_id, deleted) values ('user2', 'well2', 0);
CREATE TABLE
CREATE INDEX
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
select * from table1
| id | user_id | well_id | deleted |
|---|---|---|---|
| 1 | user1 | well1 | 0 |
| 2 | user1 | well1 | 1 |
| 3 | user1 | well1 | 1 |
| 4 | user2 | well2 | 0 |
SELECT 4
insert into table1 (user_id, well_id, deleted) values ('user1', 'well1', 0);
ERROR: duplicate key value violates unique constraint "table1_user_id_well_id_idx" DETAIL: Key (user_id, well_id)=(user1, well1) already exists.