WHERE bing = 't'
Posted Wednesday Jun 23rd, 2010 02:36pm
by Joshua Drake | Permalink



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.

---

blog comments powered by Disqus