1

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?

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

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.