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

Entering 9 days of PostgreSQL Dimension
Posted Friday Jun 4th, 2010 01:09pm
by Joshua Drake | Permalink



On the 6th, I leave town, travelling again to an unknown land. A land of mystery, a land of great mobster movies and incredible picture opportunities. Of course, I speak of Chicago where I will be delivering a 5 day training on our illustrious database, PostgreSQL. After the 5 day training, I will be taking a short jaunt to South East Linux Fest where I will be speaking on PostgreSQL Performance. The PostgreSQL Performance talk has been getting increasingly popular. I attribute the popularity to a couple of things. One, I don't get mired in the academics of performance. It isn't about finding that final Gentoo inspired 2%. It is about solving the 90% problem. What are the meat and potatoes of PostgreSQL performance and provisioning? It seems especially popular with those who don't want to be a DBA but still want to be confident that their installation is doing a above average job of being configured. A lot of database people at this point are going, "Excuse me? Above Average? It must be exacting in its performance profile!". I say bosh. Give me a great performing database that is above average so I can actually be productive chasing squirrels through New York's central park over agonizing over that 2% any day. Here is a tip. If you want that extra 2% and you want it quickly, buy new hardware. Don't spend weeks of man hours trying to find it.

---

blog comments powered by Disqus