How to enforce a column value on duplicate values on postgresql using constraint?
A partial index like suggested by @ypercube is likely the best solution. If you for some reason would like to keep it at the logical level you can generate a column:
If ACTIVE: concatenate the &quot;partially unique&quot; columns, I used a token separator to distinguish columns, result of 'a' || 'bc' should be different from 'ab' || 'c'
If NOT ACTIVE: use the primary key instead of the &quot;unique&quot; columns
I used xyz as the name of the generated column because I did not know better:-)
create table mytable
( id serial primary key
, action integer not null
, model text not null
, model_id integer not null
, active boolean not null
, xyz text GENERATED ALWAYS AS ( case when active 
                                      then action::text 
                                        || '#' 
                                        || model 
                                        || '#' 
                                        || model_id::text 
                                     else id::text 
                                 end
                               ) stored
, unique (xyz)                          
);