Add constraint to multiple columns only if one of them is true

I have this table

CREATE TABLE user (
    user_id BIGINT NOT NULL,
    modified_date DATE NOT NULL,
    is_relevant BOOLEAN NOT NULL,
    more columns...
);

I have a table of users, and I save history on users. when a data on a user is updated, I insert a new row, with the modified date, with the same user_id, but with is_relevant true. Only the last updated one is with true on is_relevant. So each update a user has, I update the prev row to false, and insert new row with true.

I want to add a constraint for a unique key on user_id and is_relevant, only if is_relevant is true. I want to avoid a situation of two rows with same user id, and true in is_relevant.

Anyone knows how to do it?

Answer

You need a partial unique index, e.g.:

create unique index on "user" (user_id) where is_relevant;

Read about partial indexes in the docs.

Advertisement

Answer