Re: unexpected check constraint violation

On Mon, Mar 23, 2009 at 2:52 PM, Jacek Becla <becla@slac.stanford.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.