CODEWITHSUNDEEP
postgresqlindexing
samol
samol

Reputation: 20570

Postgres: Composite Unique Index with a value

CREATE TABLE article (
   day integer,
   is_chosen boolean
)
CREATE UNIQUE INDEX day_is_chosen_unique_index ON article (day, is_chosen);

The above will give me an unique index on the two columns, which means on a given day we can have one article that is chosen and one article that is not chosen.

I need to the unique index for the is_chosen field only. In other words, on any given day, we can only have one article that is chosen and multiple articles that are not chosen

Maybe something like this:

CREATE UNIQUE INDEX day_is_chosen_unique_index ON article (day, is_chosen true);

How would I go about creating this composite index?

Upvotes: 1

Views: 1130

Answers (1)

Tomasz Siorek
Tomasz Siorek

Reputation: 711

This partial index will suffice:

CREATE TABLE article (
    id          INT
,   when_day    INT
,   is_chosen   BOOLEAN DEFAULT FALSE
);

CREATE UNIQUE INDEX day_is_chosen_unique_index
    ON article ( when_day )
    WHERE is_chosen;

Note: 'DAY' is reserved keyword in SQL so better not to use it as a column name.

Upvotes: 1

Related Questions