Hi, > ERROR: syntax error at or near "SELECT"
This error message is from PostgreSQL, not H2. You need to ask at the PostgreSQL group. Regards, Thomas On Friday, April 12, 2013, wrote: > When I try that sample (and other variations) it complains about having > the select in the check (specifically, > > Error executing SQL ALTER TABLE password_tokens ADD CONSTRAINT > active_password_token_check CHECK ( > SELECT count(*) FROM password_tokens p1, password_tokens p2 > WHERE p1.consumed=true > AND p2.consumed=true > AND p1.expiry > now() > AND p2.expiry > now() > > ) <=1;: ERROR: syntax error at or near "SELECT" > > It *is* possible to do a query inside a check, right? > > On Friday, April 12, 2013 2:23:42 AM UTC-7, Noel Grandin wrote: >> >> Something like: >> >> ALTER TABLE password_tokens >> ADD CONSTRAINT active_password_token_check >> CHECK ( >> SELECT count(*) FROM password_tokens p1, password_tokens p2 >> WHERE p1.user_id=p2.user_id >> AND p1.consumed=true >> AND p1.consumed = true >> AND p1.expiry>now() >> AND p2.expiry>now() >> ) <= 1 >> >> >> On 2013-04-12 01:00, tim.gr...@seekersolutions.**com wrote: >> >> Hello >> >> I'm trying to port some things over from a postgres database we have. >> I'm stuck on a check constraint on one of our postgres tables. Basically I >> need to see if any other rows in the table would prevent the current one >> from being inserted. >> >> In this case, two rows cannot >> * share a user id, >> * have "consumed" true, AND >> * have an expiry date some time in the future. >> >> in postgres, I'd use a function, and pass values from the row into the >> function, which would perform a SELECT over the table to see if any >> existing rows would prevent this row from being inserted. >> >> CREATE OR REPLACE FUNCTION check_password_reset_token(**int) RETURNS >> boolean AS ' >> >> SELECT count(*)>0 >> FROM password_tokens >> WHERE (user_id=$1 AND consumed=true AND expiry>now()); >> >> ' LANGUAGE SQL; >> >> ALTER TABLE password_tokens ADD CONSTRAINT active_password_token_check >> CHECK (check_password_reset_token(**user_id)); >> >> When I try the above code in H2, it complains about not understanding >> functions, but when i try to move the whole thing into the check constraint >> in the alter table statement, then I get stuck on actually passing the >> row's values into the parts of the SELECT up there. >> >> I know h2 supports Java functions, so that's a last resort, but we're >> only using h2 for in-memory testing and I don't want to have to test the >> test code, as it were. Is there another way to perform the same check >> constraint in h2? >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to h2-database...@**googlegroups.com. >> To post to this group, send email to [email protected]. >> Visit this group at >> http://groups.google.com/**group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >> . >> For more options, visit >> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >> . >> >> >> >> >> -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] <javascript:_e({}, > 'cvml', 'h2-database%[email protected]');>. > To post to this group, send email to > [email protected]<javascript:_e({}, 'cvml', > '[email protected]');> > . > Visit this group at http://groups.google.com/group/h2-database?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
