Thread: table constraint + INSERT

<p><font size="2">Hello<br /><br /> I use PostgreSQL in my program and I found a strange behavior, at least for me.<br
/><br/> I have a simple table with constraint<br /><br /> CREATE TABLE "PART"<br /> (<br />   "P_PARTKEY" int4 NOT
NULL,<br/>   "P_RETAILPRICE" numeric,<br />   CONSTRAINT "PART_PRIMARY" PRIMARY KEY ("P_PARTKEY"),<br />   CONSTRAINT
"PART_check"CHECK ("P_RETAILPRICE" = (90000 + "P_PARTKEY" / 10 + "P_PARTKEY" / 100)<br /> );<br /><br /> And I try to
inserta row:<br /> INSERT INTO "PART" ("P_PARTKEY","P_RETAILPRICE") VALUES(999,90109.89);<br /><br /> but it fails:
ERROR: new row for relation "PART" violates check constraint "PART_check"<br /><br /> When you check using your head or
pocketcalculator then this INSERT seems to be correct. Is it some floating point mystery?<br /> Is there some trick?<br
/>I'm using version 8.0.1 for Windows.<br /><br /> With thanks, Risto<br /><br />  </font> 

>  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

Too bad, some code got truncated...

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 + P_PARTKEY::numeric / 100
);

and the second code should read:

create view PARTV as
select P_PARTKEY, 90000 + P_PARTKEY::numeric / 10 + P_PARTKEY::numeric
/ 100 as P_RETAILPRICE
from PART;

--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org