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 2FA709FA226 for ; Thu, 18 May 2006 04:37:23 -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 30767-03 for ; Thu, 18 May 2006 04:37:12 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id 2A3089FA218 for ; Thu, 18 May 2006 04:37:12 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id y38so80587nfb for ; Thu, 18 May 2006 00:37:11 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=eHRS+UPOnnwZSQpxKTJTZlQcw2Uu4JadZvw3V7aPwNK+fKm0XUOHepxwi5nclL5xS6OhJYQnz0GWHxmk43aPZ+yzgdEsVi4J7mwtQvZn8zqsroEG2FGtsEuSjLCmmcvJj2IoxksaKYC5OYZRDmG5lR3UHaXitcMWwCPGAofAkzM= Received: by 10.49.80.10 with SMTP id h10mr284046nfl; Thu, 18 May 2006 00:37:09 -0700 (PDT) Received: by 10.49.40.7 with HTTP; Thu, 18 May 2006 00:37:09 -0700 (PDT) Message-ID: <5d0f60990605180037q5e612fdfja7df044136ac70a1@mail.gmail.com> Date: Thu, 18 May 2006 09:37:09 +0200 From: "Dirk Jagdmann" To: "Risto Tamme" Subject: Re: table constraint + INSERT Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/132 X-Sequence-Number: 25163 > 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" =3D (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 constra= int > "PART_check" > > When you check using your head or pocket calculator then this INSERT see= ms > to be correct. Is it some floating point mystery? > Is there some trick? Postgres is likely doing integer arithmetic: test=3D# 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 =3D (90000 + P_PARTKEY::numeri= c / 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 / 10= 0 as from PART; --=20 ---> Dirk Jagdmann ----> http://cubic.org/~doj -----> http://llg.cubic.org