Thread: Table constraints and INSERT
Hello I use PostgreSQL in my program and I found a strange behavior, at least for me. I have a simple table with constraint CREATE TABLE "PART" ( "P_PARTKEY" int4 NOT NULL, "P_RETAILPRICE" numeric, CONSTRAINT "PART_PRIMARY" PRIMARY KEY ("P_PARTKEY"), CONSTRAINT "PART_check"CHECK ("P_RETAILPRICE" = (90000 + "P_PARTKEY" / 10 + "P_PARTKEY" / 100) ); And I try to insert a row: 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" 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? I'm using version 8.0.1 for Windows. With thanks, Risto
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
Niklas Johansson <spot@tele2.se> writes: > You must cast the integer column to a float or numeric, try: Since the CHECK is expecting an exact result, better use numeric. regards, tom lane