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_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