Обсуждение: unexpected check constraint violation

Hi,

Can someone explain why postgres complains in this case:

create table t(d real, check(d>=0.00603));
insert into t values (0.00603);

ERROR:  new row for relation "t" violates check constraint "t_d_check"

thanks
Jacek

Jacek Becla wrote:
> create table t(d real, check(d>=0.00603));
> insert into t values (0.00603);
>
> ERROR:  new row for relation "t" violates check constraint "t_d_check"

Because equality is not well-defined for "real" values?

- Jeremy

On Mar 23, 2009, at 1:41 PM, Jeremy Harris wrote:
> Because equality is not well-defined for "real" values?

That was my first thought, too, but why would two identical real
literals evaluate to different bit patterns?

On Mar 23, 2009, at 2:54 PM, Jacek Becla wrote:

> Hi,
>
> Can someone explain why postgres complains in this case:
>
> create table t(d real, check(d>=0.00603));
> insert into t values (0.00603);
>
> ERROR:  new row for relation "t" violates check constraint "t_d_check"
>
> thanks
> Jacek


try this:

insert into t values (0.00603::real);

Ries

On Mon, Mar 23, 2009 at 1:54 PM, Jacek Becla <becla@slac.stanford.edu> wrote:
> Hi,
>
> Can someone explain why postgres complains in this case:
>
> create table t(d real, check(d>=0.00603));
> insert into t values (0.00603);
>
> ERROR:  new row for relation "t" violates check constraint "t_d_check"

Without any casting, 0.00603 likely evaluates to a numeric.

select 0.00603::numeric > 0.00603::real;
 ?column?
----------
 t

So, this works:

 create table t(d real, check(d>=0.00603::real));
insert into t values (0.00603);
INSERT 0 1

Thanks Ries. Do you know if that is a postgres feature or a bug?

In practice, I wanted to load the data from a file using
COPY FROM. Modifying a large csv file in impractical and
not very elegant.

thanks,
Jacek



ries van Twisk wrote:
>
> On Mar 23, 2009, at 2:54 PM, Jacek Becla wrote:
>
>> Hi,
>>
>> Can someone explain why postgres complains in this case:
>>
>> create table t(d real, check(d>=0.00603));
>> insert into t values (0.00603);
>>
>> ERROR:  new row for relation "t" violates check constraint "t_d_check"
>>
>> thanks
>> Jacek
>
>
> try this:
>
> insert into t values (0.00603::real);
>
> Ries
>
>
>
>

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.

Scott Marlowe <scott.marlowe@gmail.com> writes:
> You can either cast the check constraint, or change the field type to
> match double precision.

The short answer here is that 0.00603::double precision and
0.00603::real are unlikely to be exactly the same value, and
which one is greater is a matter of which direction the real
got rounded off in.  On my machine the former is a bit larger:

regression=# select 0.00603::double precision - 0.00603::real;
       ?column?
----------------------
 1.85072421797494e-10
(1 row)

but on another platform it could be the other way around.

            regards, tom lane