CONSTRAINT problems

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

-Michael
_________________________________________________________________
     http://fastmail.ca/ - Fast Free Web Email for Canadians
>From pgsql-bugs-owner@postgresql.org  Wed Jan 10 20:15:33 2001
Received: from megazone23.bigpanda.com (rfx-64-6-210-138.users.reflexcom.com [64.6.210.138])
	by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0B1FUB77367
	for <pgsql-bugs@postgresql.org>; Wed, 10 Jan 2001 20:15:30 -0500 (EST)
	(envelope-from sszabo@megazone23.bigpanda.com)
Received: from localhost (sszabo@localhost)
	by megazone23.bigpanda.com (8.11.1/8.11.1) with ESMTP id f0B1FPl76360;
	Wed, 10 Jan 2001 17:15:25 -0800 (PST)
Date: Wed, 10 Jan 2001 17:15:25 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: michael@fastmail.ca
cc: pgsql-bugs@postgresql.org
Subject: Re: CONSTRAINT problems
Message-ID: <Pine.BSF.4.21.0101101713220.76348-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200101/41
X-Sequence-Number: 415

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