Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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




pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group