X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 519F79FA226
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 17 May 2006 08:49:51 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 79596-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 17 May 2006 08:49:44 -0300 (ADT)
X-Greylist: delayed 01:00:00.035899 by SQLgrey-
Received: from swip.net (mailfe07.swip.net [212.247.154.193])
	by postgresql.org (Postfix) with ESMTP id 37D1B9F9C18
	for <pgsql-sql@postgresql.org>; Wed, 17 May 2006 08:49:43 -0300 (ADT)
X-T2-Posting-ID: q0GPJAWg7ADSoZUTpWu14w==
X-Cloudmark-Score: 0.000000 []
Received: from [84.19.128.181] (account mj374011@tele2.se HELO [192.168.1.10])
	by mailfe07.swip.net (CommuniGate Pro SMTP 5.0.8)
	with ESMTPA id 198333426; Wed, 17 May 2006 12:49:41 +0200
In-Reply-To: <E763584E741976409B9F835B475B00FC1A5353@pe2850.EKTACO.local>
References: <E763584E741976409B9F835B475B00FC1A5353@pe2850.EKTACO.local>
Mime-Version: 1.0 (Apple Message framework v750)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <4FECA253-3236-4619-B317-765B4CBE9DDD@tele2.se>
Cc: <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Niklas Johansson <spot@tele2.se>
Subject: Re: Table constraints and INSERT
Date: Wed, 17 May 2006 12:49:38 +0200
To: Risto Tamme <risto@ektaco.ee>
X-Mailer: Apple Mail (2.750)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200605/118
X-Sequence-Number: 25149


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