Re: Regular expression in an if-statement will not work - Mailing list pgsql-general

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