> 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_checkCHECK (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