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(at)rice(dot)edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005