From: Dirk Jagdmann <jagdmann@gmail.com>
To: Risto Tamme <risto@ektaco.ee>
Cc: pgsql-sql@postgresql.org
Subject: Re: table constraint + INSERT
Date: Thu, 18 May 2006 09:37:09 +0200
Message-ID: <5d0f60990605180037q5e612fdfja7df044136ac70a1@mail.gmail.com> (raw)
In-Reply-To: <E763584E741976409B9F835B475B00FC02DEB4@pe2850.EKTACO.local>
References: <E763584E741976409B9F835B475B00FC02DEB4@pe2850.EKTACO.local>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jagdmann@gmail.com, risto@ektaco.ee
Subject: Re: table constraint + INSERT
In-Reply-To: <5d0f60990605180037q5e612fdfja7df044136ac70a1@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox