Re: Check/unique constraint question

On Mar 5, 2006, at 17:25 , Jeff Frost wrote:

> And would like to make a unique constraint which would only check  
> the uniqueness of id if active=true.

I believe you're looking for what is called a partial index.

http://www.postgresql.org/docs/current/interactive/indexes-partial.html

Note, I've added a foo_id column to make sure each row is unique.  
(Duplicates are a Bad Thing.)

create table foo
(    foo_id serial not null    , id integer not null    , active boolean not null

);

create unique index foo_partial_idx on foo (id) where active;

insert into foo (id, active) values (5, false);
insert into foo (id, active) values (5, false);
insert into foo (id, active) values (5, true);
insert into foo (id, active) values (6, false);
insert into foo (id, active) values (6, true);

select * from foo;

foo_id | id | active
--------+----+--------      1 |  5 | f      2 |  5 | f      3 |  5 | t      4 |  6 | f      5 |  6 | t
(5 rows)

insert into foo (id, active) values (5, true);
ERROR:  duplicate key violates unique constraint "foo_partial_idx"

Michael Glaesemann
grzm myrealbox com