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 = 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=# insert into test1 values ( 3.70000000, 2.23567567, 8.27199998 );
ERROR: new row for relation "test1" violates check constraint
"test1_check"
btcx=# insert into test1 values ( 3.70000000, 2.23567567,
2.23567567*3.70000000 );
ERROR: new row for relation "test1" violates check constraint
"test1_check"
btcx=# 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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs