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?

• ypercubeᵀᴹ: Partial index: CREATE UNIQUE INDEX index_name ON mytable (model, model_id, action) WHERE (active)
• Jim D: The partial index solution is a good one. An alternative that might be useful is a materialized view with a unique constraint.
• Dimitrios Desyllas: @ypercubeTM Do you know how I can list any value where has not been indexed as well?
• ypercubeᵀᴹ: Not sure what you mean? SELECT * FROM mytable WHERE active = FALSE ; ?

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) 
);