CONSTRAINT problems

From: "Michael Richards" <michael(at)fastmail(dot)ca>
To: pgsql-bugs(at)postgresql(dot)org
Subject: CONSTRAINT problems
Date: 2001-01-11 00:52:23
Message-ID: 3A5D03C7.000011.86746@frodo.searchcanada.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(at)postgresql(dot)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(at)postgresql(dot)org>; Wed, 10 Jan 2001 20:15:30 -0500 (EST)
(envelope-from sszabo(at)megazone23(dot)bigpanda(dot)com)
Received: from localhost (sszabo(at)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(at)megazone23(dot)bigpanda(dot)com>
To: michael(at)fastmail(dot)ca
cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: CONSTRAINT problems
Message-ID: <Pine(dot)BSF(dot)4(dot)21(dot)0101101713220(dot)76348-100000(at)megazone23(dot)bigpanda(dot)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

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2001-01-11 01:54:31 Error in Constraint Checks with PGSQL 7.03
Previous Message Peter Mount 2001-01-10 22:22:04 Re: JDBC Driver Authentication Bug