Postgres: Composite Unique Index with a value

Question

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?

"we can have one article that is chosen and one article that is not chosen" Looks more like you can have many days, each of which can be both chosen and not chosen. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Feb 21, 2014 at 23:22
@samol Did you try the proposed solution? Consider accepting the answer. — Tomasz Siorek
– Tomasz Siorek, Commented Aug 23, 2014 at 19:59

Tomasz Siorek · Accepted Answer · 2014-08-23 19:56:30Z

1

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.

edited Aug 23, 2014 at 19:56

answered Feb 21, 2014 at 22:17

Tomasz Siorek

7215 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

flyingL123 Over a year ago

Here's the documentation for partial indexes: postgresql.org/docs/8.0/static/indexes-partial.html

Collectives™ on Stack Overflow

Postgres: Composite Unique Index with a value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related