On 9 September 2010 12:55, Ungermann Carsten
<carsten.ungermann@ib-ungermann.de> wrote:
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;
How about this:
IF new.value !~ E'^\\d{1,3}$' THEN
Or if you only want to check the 3digit value, without any trigger, IMHO this is much simpler:
alter table x add constraint check_is_3_digit check ( value ~ E'^\\d{3}$' );
regards
Szymon Guz