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