create a composite unique index when column=value-CodePudding

I have an index on a table that is working fine

CREATE UNIQUE INDEX uk_index ON my_table (nbr, extract(year from date_));

now something new came up and the trigger only works when the new entry is accepted, I added a new column accepted, I can add as many entries as I wanted with the same nbr and same year as long as they are not accepted, but only one can have the same nbr, year and be accepted, something like this

CREATE UNIQUE INDEX uk_index ON my_table (case when accepted=1 then nbr, extract(year from date_));

CodePudding user response：

You have to add the same condition for another column also with complete CASE syntax -

CREATE UNIQUE INDEX uk_index ON my_table (CASE WHEN accepted=1 THEN nbr ELSE NULL END, 
                                          CASE WHEN accepted=1 THEN EXTRACT(YEAR FROM date_) ELSE NULL END);