Re: unexpected check constraint violation

On Mon, Mar 23, 2009 at 2:52 PM, Jacek Becla <becla(at)slac(dot)stanford(dot)edu> wrote:
> Thanks Ries. Do you know if that is a postgres feature or a bug?

It's not a bug, it's lack of precision in the definition on your part
being interpreted by pgsql. When you create the table, you get this:

create table t(d real, check(d>=0.00603));
\d t
Table "public.t"
Column | Type | Modifiers
--------+------+-----------
d | real |
Check constraints:
"t_d_check" CHECK (d >= 0.00603::double precision)

Note that having not been told the type for the check constraint,
pgsql defaults to double precision. So, in effect, your table
creation was this:

create table t(d real, check(d>=0.00603::double precision));

You can either cast the check constraint, or change the field type to
match double precision.

create table t(d double precision, check(d>=0.00603::double precision));
create table t(d real, check(d>=0.00603::real));

Either of those will work properly.