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?
Postgres is likely doing integer arithmetic: test=# select 90000+999/10+999/100; ?column? ---------- 90108 (1 row) So you have to cast your check constraint to numeric types: 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::numeric / 10 ); However if this would be your real SQL Schema I'd recommend using a view to calculate the R_RETAILPRICE column: CREATE TABLE PART ( P_PARTKEY int4 NOT NULL, CONSTRAINT PART_PRIMARY PRIMARY KEY (P_PARTKEY) ); create view PARTV as select P_PARTKEY, 90000 + P_PARTKEY::numeric / 10 + P_PARTKEY::numeric / 100 as from PART; -- ---> Dirk Jagdmann ----> http://cubic.org/~doj -----> http://llg.cubic.org ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
