I need to verify the value of a column by a regular expression in an if-statement before insert or update. It should be a one to three digit value. I wrote a trigger and a trigger function. In the trigger function I want to use the following if-statement to verify the value:
IF new.value !~ E'^\d{1,3}$' THEN RAISE EXCEPTION '...some text...'; END IF;
It will not work! Why?
To find my mistake I have made some additional tests:
'^[0-9]$' -- works for a one digit value '^[0-9][0-9]$' -- works for a two digit value
'^[0-9][0-9][0-9]$' -- works for a three digit value
'^[0-9]{1,3}$' -- don't work
'^[0-9]+$' -- don't work
'^[0-9]*$' -- don't work
I hope there is anybody who can help me to solve my problem and explain my mistake! In my opinion all the regular expressions do lost the same.
