Dear postgres list,
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.
Thanks in advanced,
Carsten Ungermann