pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSub Select inside Check ?
3+ messages / 3 participants
[nested] [flat]
* Sub Select inside Check ?
@ 2003-02-25 05:48 Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 2 replies; 3+ messages in thread
From: Rudi Starcevic @ 2003-02-25 05:48 UTC (permalink / raw)
To: pgsql-sql
Hi,
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 ?
Thank you kindly
Regards
Rudi.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Sub Select inside Check ?
@ 2003-02-25 11:29 Tomasz Myrta <jasiek@klaster.net>
parent: Rudi Starcevic <rudi@oasis.net.au>
1 sibling, 0 replies; 3+ messages in thread
From: Tomasz Myrta @ 2003-02-25 11:29 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; +Cc: pgsql-sql
Rudi Starcevic wrote:
> Hi,
>
> 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 ?
Postgresql 7.3 documentation:
"SQL Commands/CREATE TABLE":
...Currently, CHECK expressions cannot contain subselects nor refer to
variables other than columns of the current row...
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Sub Select inside Check ?
@ 2003-02-25 15:50 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Rudi Starcevic <rudi@oasis.net.au>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2003-02-25 15:50 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-02-25 15:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-25 05:48 Sub Select inside Check ? Rudi Starcevic <rudi@oasis.net.au>
2003-02-25 11:29 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-25 15:50 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox