This a known problem which is caused by subselects in check constraints.
Current sources seem to give an error message at create time:
ERROR: Cannot use subselect in CHECK clause
In general subselects in check constraints also may constrain tables
that the subselect mentions. In your case this wouldn't probably be
a problem, but it's difficult to tell the cases apart.
You can probably do this with a before insert/update trigger in
pl/pgsql at the current time though.
> I've got a test table in 7.0.3 where I'm implementing a directory
> type structure in a RCS type system. I have a check to ensure that
> there are no duplicate filenames within for a specific directory
> given the revisionid of 0 (means it's the current revision rather
> than a historical one).
>
> CREATE TABLE test (
> id int4 primary key,
> name VARCHAR(20),
> revision int4 default 0,
> directoryid int4,
> CONSTRAINT dupename CHECK (NOT EXISTS (SELECT name FROM test as o
> WHERE o.name!=name AND revision=0))
> );
> CREATE
> insert into test values (1,'test','0',NULL);
> ERROR: ExecEvalExpr: unknown expression type 108