Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id F0CDD2E0035 for ; Fri, 15 Feb 2008 03:27:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 66184-01 for ; Fri, 15 Feb 2008 03:27:26 -0400 (AST) Received: from mx.inode.at (mx12.lb01.inode.at [62.99.145.14]) by postgresql.org (Postfix) with ESMTP id 3348F2E002C for ; Fri, 15 Feb 2008 03:27:29 -0400 (AST) Received: from [85.127.134.231] (port=3284 helo=hades) by smartmx-12.inode.at with esmtpa (Exim 4.50) id 1JPuyj-0007vC-6J for pgsql-sql@postgresql.org; Fri, 15 Feb 2008 08:27:25 +0100 From: "Horst Dehmer" To: Subject: inhibit rounding on numeric columns Date: Fri, 15 Feb 2008 08:28:41 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0000_01C86FAC.C5F20610" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AchvpGN962w3bREtQ1iAA6+c2oLs7Q== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 Message-Id: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/110 X-Sequence-Number: 30201 This is a multi-part message in MIME format. ------=_NextPart_000_0000_01C86FAC.C5F20610 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi! Is there an easy way to enforce strict handling of numeric values with scales, i.e. raise an exception/error instead of rounding values to the specified scale? In a given schema with 250+ tables I have lots of numeric columns with a scale > 0. The docs (chapter 8.1.2) state clearly that greater scales are rounded: "If the scale of a value to be stored is greater than the declared scale of the column, the system will round the value to the specified number of fractional digits."

This works as designed:


create table dummy (pi numeric(5,4));
insert into dummy values(3.141);  -- insert #1
insert into dummy values(3.1415);  -- insert #2
insert into dummy values(3.14159);  -- insert #3
insert into dummy values('3.14159');  -- insert #4
 
postgres=# select * from dummy;
   pi
--------
 3.1410
 3.1415
 3.1416
 3.1416
(4 rows)

I wonder if there is a generic/transparent way (say a config parameter) to force postgresql to raise an error for inserts #3 and #4. If there is no easy way, what other alternatives exist? Sadly, changing the schema is hardly a possibility for me :-( Any suggestions are appreciated! Note: Using PostgreSQL 8.3 with JDBC. -- Horst Dehmer ------=_NextPart_000_0000_01C86FAC.C5F20610 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi!

Is = there an easy way=20 to enforce strict handling of numeric values with scales, i.e. raise an=20 exception/error instead of rounding values to the specified=20 scale?

In a = given schema=20 with 250+ tables I have lots of numeric columns with a scale > = 0. The=20 docs (chapter 8.1.2) state clearly that greater scales are rounded: = "If the scale of a value to be = stored is=20 greater than the declared scale of the column, the system will round the = value=20 to the specified number of fractional = digits."

<p>

This = works as=20 designed:

<p>

<pre>

<code>

create = table dummy=20 (pi numeric(5,4));
insert into dummy values(3.141); -- insert=20 #1
insert into dummy values(3.1415); -- insert #2
insert = into dummy=20 values(3.14159); -- insert #3
insert into dummy=20 values('3.14159'); -- insert #4

postgres=3D# select *=20 from dummy;
=20 pi
--------
3.1410
3.1415
3.1416
3= .1416
(4=20 rows)

</code>

</pre><= /SPAN>

I = wonder if there is=20 a generic/transparent way (say a config parameter) to force postgresql = to raise=20 an error for inserts #3 and #4. If there is no easy way, what other = alternatives=20 exist? Sadly, changing the schema is hardly a possibility for=20 me :-(

Any = suggestions are=20 appreciated!

Note: = Using=20 PostgreSQL 8.3 with JDBC.

-- = Horst=20 Dehmer

------=_NextPart_000_0000_01C86FAC.C5F20610--