How to enforce a column value on duplicate values on postgresql using constraint? I have the following table:
mytable
---------
id Serial PK
action Integer
model VARCHAR
model_id integer
active BOOL

But due to my logic there cannot be duplicate values having the same values for model,model_id,action when active is true.
Therefore, I place a unique constraint:
ALTER TABLE mytable ADD CONSTRAINT constraint_name UNIQUE (model,model_id,action,active);

But that does not avoid me having the unique constraint only when active=true. Also, my table is pre-populated with duplicate values therefore I want to set active=false on dduplicate ones and keep only one record with value=true do you know any good idea on how I can do this beyond just writing an update statement before applying the 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)                          
);