On 17 maj 2006, at 08.42, Risto Tamme wrote:
INSERT INTO "PART" ("P_PARTKEY","P_RETAILPRICE") VALUES(999,90109.89);
but it fails: ERROR: new row for relation "PART" violates check
constraint "PART_check"
The P_PARTKEY column is an integer, which means the expression
P_PARTKEY/10 will yield 99, *not* 99.9.
Try executing
SELECT 90000 + 999/10 + 999/10;
from psql or some GUI-utility; the result is 90108.
When you check using your head or pocket calculator then this INSERT
seems to be correct. Is it some floating point mystery?
Is there some trick?
You must cast the integer column to a float or numeric, try:
SELECT 90000 + 999::numeric/10 + 999::numeric/100;
In your case:
CHECK ("P_RETAILPRICE" = (90000 + "P_PARTKEY"::numeric / 10 +
"P_PARTKEY"::numeric / 100)
Sincerely,
Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
