X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 09494475E30
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 11:04:45 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 49768-07 for <pgsql-sql@postgresql.org>;
	Tue, 25 Feb 2003 11:04:34 -0500 (EST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 5593B475E3A
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 10:50:09 -0500 (EST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id D59D9D5F7; Tue, 25 Feb 2003 07:50:13 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id CB63C5C02; Tue, 25 Feb 2003 07:50:13 -0800 (PST)
Date: Tue, 25 Feb 2003 07:50:13 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Rudi Starcevic <rudi@oasis.net.au>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Sub Select inside Check ?
In-Reply-To: <3E5B03B9.4070500@oasis.net.au>
Message-ID: <20030225074841.X57635-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/553
X-Sequence-Number: 11951


On Tue, 25 Feb 2003, Rudi Starcevic wrote:

> I'm trying to create this table with a Check that fails.
>
> I'm using 'Check' instead of 'References' due to poor db schema ( before
> me .. )
>
> Here is my error message:
> ERROR: cannot use subselect in CHECK constraint expression
>
> CREATE TABLE cp_retailers
> (
> ret_id serial PRIMARY KEY,
> ret_name varchar(120) NOT NULL,
> ret_address1 varchar(120),
> ret_address2 varchar(120),
> sub_id integer NOT NULL CHECK
>                             ( EXISTS
>                             ( SELECT sub_id FROM suburbs )
>                             )
> );
>
> I guess that say's it all - no sub select's in Check or am I on the
> wrong track ?

Unfortunately, that's not supported (it's non-trivial).  If you're
looking only for an insert/update on cp_retailers check (ie, suburbs
is unlikely to be updated/deleted from), you can do this by hiding
the subselect in a function and using that in the check.  If you
need both direction checks it gets rather complicated to do well, but
you can come fairly close using after triggers.