Re: POSIX-style regular expressions

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