Regular expression in an if-statement will not work

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;

It will not work! Why?

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

I tried it once more, now. There is no change in the behavior. I use the $$-notation to avoid double "\" and double "'". Also there is no syntax error in this statement.
The constraint is a bad option because I have to log errors when I import a csv-file.

Thanks and regards
Carsten Ungermann

Am 09.09.2010 um 15:44 schrieb Szymon Guz:

On 09/09/10 11:55, Ungermann Carsten 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.

'^[0-9]{1,3}$' -- don't work

Works here.

CREATE TEMP TABLE tt (t text);
INSERT INTO tt VALUES ('1'),('12'),('123'),('1234'),(' 123'),('123 ');
SELECT ':' || t || ':' AS target, t ~ '^[0-9]{1,3}$' FROM tt;
target | ?column?
--------+----------
:1: | t
:12: | t
:123: | t
:1234: | f
: 123: | f
:123 : | f
(6 rows)

Works in 8.2, 8.3, 8.4, 9.0 for me. Either you're not testing the values
you think you are, or there is some issue with escaping of characters.

--
Richard Huxton
Archonet Ltd

I have discovered the reasons of my problem.

Firstly I made a mistake at the type declaration of the column "value". It was "character(3)". So that missing characters were filled with spaces and the regular expression in case of less than three digits couldn't match at the end ("$"). I changed the type to "character varying(3)". Not a really suitable solution, but it should be enough for testing. It works with all regular expressions like the following:

'^[0-9]{1,3}$' or '^[\x30-\x39]{1,3}$' or '^[\u0030-\u0039]{1,3}$'.

The second mistake I made with the escaping of characters. In my case four "\" are needed to write a regular expression with "\d". So '^\\\\d{1,3}$' works correct.

Thanks to all who gives me new ideas to solve the problem and kind regards,
Carsten Ungermann

Am 09.09.2010 um 17:52 schrieb Richard Huxton: