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
