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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
