Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9DF652E0035 for ; Fri, 15 Feb 2008 03:54:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 20084-09 for ; Fri, 15 Feb 2008 03:54:10 -0400 (AST) Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 04DE62E002C for ; Fri, 15 Feb 2008 03:54:09 -0400 (AST) Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Fri, 15 Feb 2008 08:54:07 +0100 Message-Id: <47B55321.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Fri, 15 Feb 2008 08:53:54 +0100 From: "Bart Degryse" To: Subject: Re: inhibit rounding on numeric columns References: In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__PartDDFB2202.0__=" X-imss-version: 2.049 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/113 X-Sequence-Number: 30204 --=__PartDDFB2202.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable You could probably create a before insert trigger which compares the = number of fractional digits in the given number with the defined scale = (surely some system table can offer you that) and raises an exception if = needed. I do agree though with Niklas Johansson's remarks and wonder with = him on what exactly you're trying to accomplish. >>> "Horst Dehmer" 2008-02-15 8:28 >>> Hi! =20 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? =20 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
=20
postgres=3D# select * from dummy;
   pi
--------
 3.1410
 3.1415
 3.1416
 3.1416
(4 rows)

=20 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! =20 Note: Using PostgreSQL 8.3 with JDBC. =20 -- Horst Dehmer --=__PartDDFB2202.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML You could probably = create a before insert trigger which compares the number of fractional = digits in the given number with the defined scale (surely some system = table can offer you that) and raises an exception if needed. I do agree = though with Niklas Johansson's remarks and wonder with him on what exactly = you're trying to accomplish.

>>> "Horst Dehmer" <horst.d= ehmer@inode.at> 2008-02-15 8:28 >>>

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."

<p>=

This = works as designed:

<p>=

<pre&g= t;

<code&= gt;

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= =3D# select * from dummy;
pi
--------
3.1410 3.1415
3.1416
3.1416
(4 rows)

</code= >

</pre>

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

--=__PartDDFB2202.0__=--