Límite de clave principal compuesta de un solo registro como "no se elimina" (donde varios "eliminado" de los registros de no invocar restricción)

Usted puede lograr la unicidad con una inmutable criterios que se deleted = 0 en su caso.

Aquí está el ejemplo de la instalación;

create table table1 (
  id serial primary key,
  user_id text, 
  well_id text, 
  deleted int
);

-- the index 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);
-- this statement doesn't throws error
insert into table1 (user_id, well_id, deleted) values ('user1', 'well1', 1);
insert into table1 (user_id, well_id, deleted) values ('user2', 'well2', 0);

select * from table1;

-- Finally, this statement throws an error
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.

db violín aquí