pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtable constraint + INSERT
3+ messages / 2 participants
[nested] [flat]
* table constraint + INSERT
@ 2006-05-16 21:01 Risto Tamme <risto@ektaco.ee>
0 siblings, 1 reply; 3+ messages in thread
From: Risto Tamme @ 2006-05-16 21:01 UTC (permalink / raw)
To: pgsql-sql
Hello
I use PostgreSQL in my program and I found a strange behavior, at least for me.
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?
I'm using version 8.0.1 for Windows.
With thanks, Risto
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: table constraint + INSERT
@ 2006-05-18 07:37 Dirk Jagdmann <jagdmann@gmail.com>
parent: Risto Tamme <risto@ektaco.ee>
0 siblings, 1 reply; 3+ messages in thread
From: Dirk Jagdmann @ 2006-05-18 07:37 UTC (permalink / raw)
To: Risto Tamme <risto@ektaco.ee>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: table constraint + INSERT
@ 2006-05-18 07:40 Dirk Jagdmann <jagdmann@gmail.com>
parent: Dirk Jagdmann <jagdmann@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dirk Jagdmann @ 2006-05-18 07:40 UTC (permalink / raw)
To: Risto Tamme <risto@ektaco.ee>; +Cc: pgsql-sql
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_check CHECK (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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-05-18 07:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-16 21:01 table constraint + INSERT Risto Tamme <risto@ektaco.ee>
2006-05-18 07:37 ` Dirk Jagdmann <jagdmann@gmail.com>
2006-05-18 07:40 ` Dirk Jagdmann <jagdmann@gmail.com>
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