Thread: BUG #8175: Check constraint fails for valid data. ( rounding related? )

The following bug has been logged on the website:

Bug reference:      8175
Logged by:          Dan Libby
Email address:      dan.libby@gmail.com
PostgreSQL version: 9.1.6
Operating system:   Linux  ( Ubuntu 12.04 )
Description:        =


-- Try this script --

create table test1 (
  val1 numeric(23,8),
  val2 numeric(23,8),
  product numeric(23,8) check( product =3D val1 * val2 )
);

select (2.23567567*3.70000000)::numeric(23,8);
insert into test1 values ( 3.70000000, 2.23567567, 8.27199998 );
insert into test1 values ( 3.70000000, 2.23567567, 2.23567567*3.70000000 );
insert into test1 values ( 3.70000000, 2.23567567,
(2.23567567*3.70000000)::numeric(23,8) );


-- Actual Results --

select (2.23567567*3.70000000)::numeric(23,8);
numeric | 8.27199998

btcx=3D# insert into test1 values ( 3.70000000, 2.23567567, 8.27199998 );
ERROR:  new row for relation "test1" violates check constraint
"test1_check"
btcx=3D# insert into test1 values ( 3.70000000, 2.23567567,
2.23567567*3.70000000 );
ERROR:  new row for relation "test1" violates check constraint
"test1_check"
btcx=3D# insert into test1 values ( 3.70000000, 2.23567567,
(2.23567567*3.70000000)::numeric(23,8) );
ERROR:  new row for relation "test1" violates check constraint
"test1_check"


-- Expected Results --

All values should be inserted successfully.

dan.libby@gmail.com writes:
> create table test1 (
>   val1 numeric(23,8),
>   val2 numeric(23,8),
>   product numeric(23,8) check( product = val1 * val2 )
> );

> select (2.23567567*3.70000000)::numeric(23,8);
> insert into test1 values ( 3.70000000, 2.23567567, 8.27199998 );
> insert into test1 values ( 3.70000000, 2.23567567, 2.23567567*3.70000000 );
> insert into test1 values ( 3.70000000, 2.23567567,
> (2.23567567*3.70000000)::numeric(23,8) );

It's not surprising that these all fail.  You'd need to make the check
be more like this:

check( product = (val1 * val2)::numeric(23,8) )

Otherwise, the check will always fail when the product has more than 8
fractional digits.  It's not Postgres' place to decide that that wasn't
what you wanted to happen.

            regards, tom lane