Thread: BUG #3056: strange behave of CHECK constraint

The following bug has been logged online:

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

Hello,

check constraint doesn't work correct:

opeerator similar works well:

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

but:
root=# create table tt(a varchar check (a not similar to '%[0-9]%'));
CREATE TABLE
root=# insert into tt values('233');
ERROR:  new row for relation "tt" violates check constraint "tt_a_check"

Regards
Pavel Stehule

Is it just me, or did you reverse the sense of your check.  In the=20=20
first case, you select with "[^0-9]", but your constraint uses=20=20
"[0-9]" without the carrot.

On Feb 22, 2007, at 4:48 PM, Pavel Stehule wrote:

>
> The following bug has been logged online:
>
> Bug reference:      3056
> Logged by:          Pavel Stehule
> Email address:      pavel.stehule@hotmail.com
> PostgreSQL version: 8.3
> Operating system:   Linux
> Description:        strange behave of CHECK constraint
> Details:
>
> Hello,
>
> check constraint doesn't work correct:
>
> opeerator similar works well:
>
> root=3D# select '233' not similar to '%[^0-9]%';
>  ?column?
> ----------
>  t
> (1 =C5=99=C3=A1dka)
>
> but:
> root=3D# create table tt(a varchar check (a not similar to '%[0-9]%'));
> CREATE TABLE
> root=3D# insert into tt values('233');
> ERROR:  new row for relation "tt" violates check constraint=20=20
> "tt_a_check"
>
> Regards
> Pavel Stehule
>
> ---------------------------(end of=20=20
> broadcast)---------------------------
> TIP 6: explain analyze is your friend

Douglas Toltzman
doug@oakstreetsoftware.com
(910) 526-5938

Pavel Stehule wrote:
> check constraint doesn't work correct:

There is nothing incorrect happening here.  I think you are confused.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/