strange behaviour of "CHECK ... IN (SELECT ...)" as constraint - Mailing list 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.