How to enforce a column value on duplicate values on postgresql using constraint?

Question

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?

Answer 1

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 "partially unique" 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 "unique" 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)                          
);