Thread

Alexei A.Romanenko <a.a.romanenko@inp.nsk.su> — 2000-06-28T05:39:19Z

Your name               : Alexei A. Romanenko
Your email address      : A.A.Romanenko@inp.nsk.su


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)  : Linux 2.2.12 ELF

  PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.2

  Compiler used (example:  gcc 2.8.0)           : 


Please enter a FULL description of your problem:
------------------------------------------------
It seems to me there is a problem with regular expressions.
When i create table and try to insert some restriction for
a fields, system accept it. Then, whem i insert something, which
dont match to regexp, it inserted anyway or backwards.

Maybe i am do something wrong, but...


Please describe a way to repeat the problem.   Please try to
provide a concise reproducible example, if at all possible:
----------------------------------------------------------------------
arom> psql
1.
arom=> create table vv (v char(10) CHECK (v ~*
'^[a-z]+\@[a-z]+'));
CREATE
arom=> INSERT INTO vv VALUES ('aq@wwww');
INSERT 19863 1
arom=> INSERT INTO vv VALUES (' aq@wwww');
ERROR:  ExecAppend: rejected due to CHECK constraint vv_v
arom=> INSERT INTO vv VALUES ('aq@2wwww');
ERROR:  ExecAppend: rejected due to CHECK constraint vv_v
arom=> INSERT INTO vv VALUES ('aq@wwww        ');
INSERT 19864 1
arom=> drop table vv;
DROP
arom=> create table vv (v char(10) CHECK (v ~* '^[a-z]+$'));
CREATE
arom=> INSERT INTO vv VALUES ('aq');
ERROR:  ExecAppend: rejected due to CHECK constraint vv_v

In POSIX standart symbol '$' means END OF LINE. Right?

2.

arom=> create table vv (v char(10) CHECK (v ~* '[:alnum:]+'));
CREATE
arom=> INSERT INTO vv VALUES ('12345');
ERROR:  ExecAppend: rejected due to CHECK constraint vv_v
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
arom=> INSERT INTO vv VALUES ('abcd');
INSERT 19895 1
arom=> INSERT INTO vv VALUES ('123abcd');
INSERT 19896 1
arom=> drop table vv;
DROP

[:alnum:] means [:alpha:] and [:digit:]. 
But [a-z0-9] works correct

If you know how this problem might be fixed, list the solution
below:
---------------------------------------------------------------------

Re:

Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-03T14:51:10Z

"Alexei A.Romanenko" <A.A.Romanenko@inp.nsk.su> writes:
> It seems to me there is a problem with regular expressions.
> When i create table and try to insert some restriction for
> a fields, system accept it. Then, whem i insert something, which
> dont match to regexp, it inserted anyway or backwards.

No bug there.  You've forgotten that a char(N) field value will be
space-padded to N characters.  For variable-length strings you
ought to be using varchar(N) instead.  If you really want to use
char(N), you can match with regexps like 'foo *$'.

			regards, tom lane