Re: table constraint + INSERT

>  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