unexpected check constraint violation

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

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

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