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. [EMAIL PROTECTED] Executive Director phone: 713-348-6166 Gulf Coast Consortium for Bioinformatics fax: 713-348-6182 Rice University MS-39 Houston, TX 77005 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster