BUG #3037: strange behave of CHECK constraint

"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/