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