Re: subselect in CHECK constraint?

* Ian Turner <vectro@pipeline.com> [000903 22:37] wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> When I try to do this:
>
> CREATE TABLE test (
>     a Integer,
>     b Integer,
>     CHECK ((SELECT SUM(t.a) FROM test t WHERE t.b = b) < 1000)
> );
>
> INSERT INTO test (a, b) VALUES (100, 2);
>
> I get this error on the second query:
>
> ERROR:  ExecEvalExpr: unknown expression type 108
>
> I'm guessing this means I can't do subselects in CHECK statements.

Two things:

1) i'm pretty sure this subselect can be rewritten as:
SELECT SUM(t.a) < 1000 FROM test t WHERE t.b = b
to return a boolean.

2) you can probably get away with using a plpgsql function
that has more logic in it.

I'm not saying that subselects do or do not work, just offering
some alternative advice.

-Alfred