Date: Tue, 10 Sep 2002 10:13:39 -0500
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: Goran Buzic <goran.buzic@hep.hr>, pgsql-sql@postgresql.org
Subject: Re: POSIX-style regular expressions
Message-ID: <20020910151339.GE1023@rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	Goran Buzic <goran.buzic@hep.hr>, pgsql-sql@postgresql.org
References: <alkea9$104o$1@news.hub.org>
	<Pine.BSO.4.44.0209100754190.3512-100000@kitten.greentechnologist.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <Pine.BSO.4.44.0209100754190.3512-100000@kitten.greentechnologist.org>
User-Agent: Mutt/1.3.27i

On Tue, Sep 10, 2002 at 08:35:27AM -0500, Josh Jore wrote:
> On Tue, 10 Sep 2002, Goran Buzic wrote:
> 
> >     id1    char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
> 
> > ERROR:    ExecAppend: rejected due to CHECK constraint table_name1_id1
> >
> > I tested preceding regular expression with Perl and JavaScript and it worked
> > fine. Can I use regular expressions with CHECK parametar, and if so, how can
> > I make it work.
> 
> You should probably test it against PostgreSQL's regex engine. What you
> may not know is that they all have different syntaxes, rules and quirks.
> What works in one may or may not work in another.
> 
> So check out src/backend/regex and build retest (I think that's what it
> was called). It's a command line regex tester (obviously against
> PostgreSQL's implementation).

Or, test directly in psql. I dropped your test data into a table, and
played with select:

test=# select * from testtable ;
   id   
   --------
    1.2.  
     1.12. 
      12.1. 
       12.12.
       (4 rows)

test=# select * from testtable ;
   id   
--------
 1.2.  
 1.12. 
 12.1. 
 12.12.
(4 rows)

test=# select * from testtable where id ~* '^([0-9]{1,2}\.){2}$';
   id   
--------
 12.12.
(1 row)

Hmm, that's because you said char(6), which is bank padded:

test=# select * from testtable where id ~* '^([0-9]{1,2}\.){2} *';
   id   
--------
 1.2.  
 1.12. 
 12.1. 
 12.12.
(4 rows)

Further testing with your actual table def (what version are you using?
I dont have ON INSERT CASCADE in my 7.2.1 test database) indicates you
need to double up the slashes on the '.', as so:

'^([0-9]{1,2}\\.){2}$'

One set of slashes gets stripped by the command processor.

Note that this _still_ requires a 6 char input, so 1.2. fails, but
01.02. works.

Ross
-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Executive Director                                  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
Rice University MS-39
Houston, TX 77005