pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable constraints and INSERT
3+ messages / 3 participants
[nested] [flat]
* Table constraints and INSERT
@ 2006-05-17 06:42 Risto Tamme <risto@ektaco.ee>
0 siblings, 1 reply; 3+ messages in thread
From: Risto Tamme @ 2006-05-17 06:42 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 constraints and INSERT
@ 2006-05-17 10:49 Niklas Johansson <spot@tele2.se>
parent: Risto Tamme <risto@ektaco.ee>
0 siblings, 1 reply; 3+ messages in thread
From: Niklas Johansson @ 2006-05-17 10:49 UTC (permalink / raw)
To: Risto Tamme <risto@ektaco.ee>; +Cc: pgsql-sql
On 17 maj 2006, at 08.42, Risto Tamme wrote:
> 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"
The P_PARTKEY column is an integer, which means the expression
P_PARTKEY/10 will yield 99, *not* 99.9.
Try executing
SELECT 90000 + 999/10 + 999/10;
from psql or some GUI-utility; the result is 90108.
> 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?
You must cast the integer column to a float or numeric, try:
SELECT 90000 + 999::numeric/10 + 999::numeric/100;
In your case:
CHECK ("P_RETAILPRICE" = (90000 + "P_PARTKEY"::numeric / 10 +
"P_PARTKEY"::numeric / 100)
Sincerely,
Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table constraints and INSERT
@ 2006-05-17 14:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Niklas Johansson <spot@tele2.se>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-05-17 14:12 UTC (permalink / raw)
To: Niklas Johansson <spot@tele2.se>; +Cc: Risto Tamme <risto@ektaco.ee>; pgsql-sql
Niklas Johansson <spot@tele2.se> writes:
> You must cast the integer column to a float or numeric, try:
Since the CHECK is expecting an exact result, better use numeric.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-05-17 14:12 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-17 06:42 Table constraints and INSERT Risto Tamme <risto@ektaco.ee>
2006-05-17 10:49 ` Niklas Johansson <spot@tele2.se>
2006-05-17 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
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