Thread: BUG #3037: strange behave of CHECK constraint

The following bug has been logged online:

Bug reference:      3037
Logged by:          Pavel Stehule
Email address:      pavel.stehule@hotmail.com
PostgreSQL version: 8.3
Operating system:   Linux
Description:        strange behave of CHECK constraint
Details:

check constraint is incorect for operator SIMILAR

root=# create table products(
  barcode char(13) NOT NULL
  check (barcode NOT similar to '%[^0-9]%')
);

root=# insert into products values('22');
ERROR:  new row for relation "products" violates check constraint
"products_barcode_check"
root=# insert into products values('aa');
ERROR:  new row for relation "products" violates check constraint
"products_barcode_check"

root=# select '22'  NOT similar to '%[^0-9]%';
 ?column?
----------
 t
(1 řádka)

"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> root=# create table products(
>   barcode char(13) NOT NULL
>   check (barcode NOT similar to '%[^0-9]%')
> );

> root=# insert into products values('22');
> ERROR:  new row for relation "products" violates check constraint
> "products_barcode_check"

> root=# select '22'  NOT similar to '%[^0-9]%';
>  ?column?
> ----------
>  t

That isn't a counterexample, because you forgot about the trailing
spaces, which can match [^0-9].

regression=#  select '22'  NOT similar to '%[^0-9]%';
 ?column?
----------
 t
(1 row)

regression=#  select '22'::char(13)  NOT similar to '%[^0-9]%';
 ?column?
----------
 f
(1 row)


            regards, tom lane

>"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> > root=# create table products(
> >   barcode char(13) NOT NULL
> >   check (barcode NOT similar to '%[^0-9]%')
> > );
>
> > root=# insert into products values('22');
> > ERROR:  new row for relation "products" violates check constraint
> > "products_barcode_check"
>
> > root=# select '22'  NOT similar to '%[^0-9]%';
> >  ?column?
> > ----------
> >  t
>
>That isn't a counterexample, because you forgot about the trailing
>spaces, which can match [^0-9].
>
>regression=#  select '22'  NOT similar to '%[^0-9]%';
>  ?column?
>----------
>  t
>(1 row)
>
>regression=#  select '22'::char(13)  NOT similar to '%[^0-9]%';
>  ?column?
>----------
>  f
>(1 row)
>
>

I see it now. Thank You. It's one trick from Joe Celko and I too late
understand it.

Nice  day
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/