Thread: subselects in check clauses
I have a need to check if a certain condition holds for all rows that matches a given criteria. But postgresql appearently doesn't support subselects in check clauses. Here is an example: CREATE TABLE example (a int, b date, c date CONSTRAINT csname CHECK (1 >= ALL (SELECT count(*) FROM example WHERE age(now(),b) > '0:0' AND age(now(),c) < '0:0' GROUP BY a))); CREATE INSERT INTO example VALUES (0,'2001-3-01','2001-12-12'); ERROR: ExecEvalExpr: unknown expression type 108 What can I do? -- Rasmus Resen Amossen | stud.mat.dat at the University of Copenhagen Jagtvej 120, -244 | http://www.math.ku.dk/muh 2200 Kbh. N | http://w1.1444.telia.com/~u144400001
On Mon, 4 Jun 2001, Rasmus Resen Amossen wrote: > I have a need to check if a certain condition holds for all rows that > matches a given criteria. But postgresql appearently doesn't support > subselects in check clauses. Here is an example: > > CREATE TABLE example (a int, b date, c date > CONSTRAINT csname CHECK (1 >= ALL (SELECT count(*) > FROM example > WHERE age(now(),b) > '0:0' > AND age(now(),c) < '0:0' > GROUP BY a))); > > CREATE > > INSERT INTO example VALUES (0,'2001-3-01','2001-12-12'); > ERROR: ExecEvalExpr: unknown expression type 108 > > What can I do? You can probably fake it by doing the check inside a trigger on insert, update and delete.