Partial Index Trick

On the channel #postgresql today a very interesting question was raised (changed for readability):

I need to create a constraint on a table. The constraint must verify the uniqueness of two fields: string and logical. However, there is an additional requirement. Only one entry with a string and a value of TRUE is allowed. At the same time, several records are allowed with a certain string, but the value is FALSE. For example, there may be several {"abc", false}, but only one line {"abc", true}.

Well, it would seem, what is the importance of this task? It's just a very good example of using the flexibility of PostgreSQL , which has in its asset a mechanism of partial indexes ( partial indices ). The solution is simple and elegant:

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');
 INTO  foobar  VALUES ('1', 'f');
INSERT  INTO  foobar  VALUES ('1', 't');
ERROR: duplicate key value violates unique constraint "baz_index"

Everything ingenious is simple. So that!