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

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.

Helge Bahmann <bahmann@math.tu-freiberg.de> writes:
>   CREATE TABLE ex2(name text, CHECK (name in (SELECT name FROM ex1)));

>   ERROR:  ExecEvalExpr: unknown expression type 108

I don't believe the constraint code knows how to cope with constraint
expressions that require planning :-(.

If you want to become a backend programming guru, a good path to
enlightenment would be to figure out how to fix that without
unacceptable performance penalties.

If you don't, I'd suggest embedding the SELECT in a plpgsql function.

            regards, tom lane