From: Andreas Seltenreich <andreas+pg@gate450.dyndns.org>
To: Sebastian Siewior <lavish@kamp-dsl.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem with a Pettern Matching Check
References: <20050816001950.2225e5b8@mikejones.ghb.fh-furtwangen.de>
Date: Tue, 16 Aug 2005 00:43:28 +0200
In-Reply-To: <20050816001950.2225e5b8@mikejones.ghb.fh-furtwangen.de>
	(Sebastian Siewior's message of "Tue, 16 Aug 2005 00:19:50 +0200")
User-Agent: Gnus/5.110004 (No Gnus v0.4) Emacs/21.4 (gnu/linux)
Message-ID: <877jemg6sv.fsf@gate450.dyndns.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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
--