Postgres: partial unique index

Question

I want to protect the integrity of a state machine with a partial unique index. For a given listing_id, there should not be more than one record with status < 3.

In other words, this is ok:

listing_id, array_agg(status)
1234, {0,3,3}

But this is not:

listing_id, array_agg(status)
1234, {0,1,3}

My first thought was something like this:

CREATE UNIQUE INDEX
  uq_state_machine_protection_per_listing_id
  ON listing_version(listing_id, status)
  WHERE status in (0,1,2);

But that's not good enough-- it only prevents multiple records with the same value of less than 3. My negative case example above would still pass the constraint.

kelorek · Accepted Answer · 2016-03-21 20:56:42Z

1

It can be dealt with by creating an index on constant. How about:

CREATE UNIQUE INDEX
  uq_state_machine_protection_per_listing_id
  ON listing_version(listing_id)
  WHERE status in (0,1,2);

edited Mar 21, 2016 at 20:56

kelorek

6,2246 gold badges32 silver badges32 bronze badges

answered Mar 21, 2016 at 18:29

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

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

1 Comment

Jakub Kania Over a year ago

@kelorek Well, if you're including listing_id you don't need the other column.

Collectives™ on Stack Overflow

Postgres: partial unique index

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