Обсуждение: Regular expression in an if-statement will not work
Dear postgres list,
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]$' -- 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
On 9 September 2010 12:55, Ungermann Carsten <carsten.ungermann@ib-ungermann.de> wrote:
regardsDear 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}$' );
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 9 September 2010 12:55, Ungermann Carsten <carsten.ungermann@ib-ungermann.de> wrote:regardsDear 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}$' THENOr 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}$' );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
characterswere 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:
> 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
withescaping of characters.
>
> --
> Richard Huxton
> Archonet Ltd
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general