I was on #postgresql today and someone asked an interesting question: (edited for readability)
I'm trying to write a constraint for a table. The constraint should check for unique-ness of two columns, one string and one boolean. However I have special logic, I can have only one row with a given string and true attribute. I can have multiple rows with the same string but with false attribute. For example, I can have many {"abc",false}, but only one {"abc",true}.Now why anyone would need this isn't important. This is a great example of PostgreSQL and flexibility. PostgreSQL has the ability to create partial indices. The solution I came up with is below:
create table foo(bar text, bing boolean);
create unique index baz_index on foobar(bar,bing) where bing = 't';
insert into foobar values('1','t');
insert into foobar values('2','t');
insert into foobar values('1','f');
insert into foobar values('1','f');
insert into foobar values('1','t');
ERROR: duplicate key value violates unique constraint "baz_index"
Exactly as it should be. Excellent.