CODEWITHSUNDEEP
sqlpostgresqlindexingconstraints
user3927415
user3927415

Reputation: 435

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 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 a 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 the same user id, and true in is_relevant.

Upvotes: 1

Views: 411

Answers (1)

klin
klin

Reputation: 121764

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.

Upvotes: 2

Related Questions