Re: Problem with a Pettern Matching Check

Sebastian Siewior schrob:

> Hello hopefully correct List,

perfectly.

> I was trying to do something that is not working as it supposed to.
> First I created a table:
>
> create table t (
> col CHAR (3) CONSTRAINT numonly_col CHECK ( col ~ '^\\d+$' )
> );
>
> This check avoids non-numbers like '1a1' and allows '123'. For some
> reason, I'm unable to find out why, it also avoids things like '1' and
> '12'. Could someone please give me hint? :)

Char is padded with spaces, and that is also why your regexp is not
matching in these situations. You could either adjust your regexp to
match the trailing spaces or use varchar(3) instead:

--8<---------------cut here---------------start------------->8---
scratch=# select '1'::char(3) ~ '^\\d+$';
?column?
----------
f
(1 row)

scratch=# select '1'::char(3) ~ '^\\d+\\s*$';
?column?
----------
t
(1 row)

scratch=# select '1'::varchar(3) ~ '^\\d+$';
?column?
----------
t
(1 row)
--8<---------------cut here---------------end--------------->8---

regards
Andreas
--