strange behaviour of "CHECK ... IN (SELECT ...)" as constraint

From: Helge Bahmann <bahmann(at)math(dot)tu-freiberg(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: strange behaviour of "CHECK ... IN (SELECT ...)" as constraint
Date: 2000-07-19 21:55:55
Message-ID: Pine.LNX.4.21.0007192354310.28741-100000@kiwi.mathe.tu-freiberg.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

I am encountering the following problem (PostgreSQL Version 7.02):

CREATE TABLE ex1(name text);
INSERT INTO ex1 VALUES('a'); INSERT INTO ex1 VALUES('b');

Now I create a second table:

CREATE TABLE ex2(name text);
INSERT INTO ex2 VALUES('a'); INSERT INTO ex2 VALUES('x');

If I issue the following SELECT statement:

SELECT * FROM ex2 WHERE name IN (SELECT name FROM ex1);

this yields the expected result (ie just 'a'). But if I try a similiar
construction as a table constraint, eg:

CREATE TABLE ex2(name text, CHECK (name in (SELECT name FROM ex1)));

Then it doesn´t work as expected; more precisely, it doesn´t work at all,
if I try:

INSERT INTO ex3 VALUES('a');

which should be perfectly legal, I do instead get the following error
message:

ERROR: ExecEvalExpr: unknown expression type 108

I suspect something is broken here, but I couldn´t find anything in the
release notes. It may also be that I'm doing something horribly wrong, but
I do not see what.

I would also be very grateful if someone could give me a hint how to
circumvent this problem, since I need such a constraint.

(In my case, ex1 is actually not a table, but instead a view; so replacing
CHECK with a REFERENCES clause doesn´t work either.)

Regards,
Helge
--
Hi! I'm a .signature virus! Put me into your .signature and help me spread!

Frueher sassen schlaue Nutzer an dummen Terminals.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2000-07-20 05:30:29 Re: strange behaviour of "CHECK ... IN (SELECT ...)" as constraint
Previous Message Tom Lane 2000-07-19 18:03:05 Re: Full text index/compiling fti.c