From: Bart Degryse <Bart.Degryse@indicator.be>
To: pgsql-sql@postgresql.org
Subject: Re: inhibit rounding on numeric columns
Date: Fri, 15 Feb 2008 08:53:54 +0100
Message-ID: <47B55321.A3DD.0030.0@indicator.be> (raw)
In-Reply-To: <E1JPuyj-0007vC-6J@smartmx-12.inode.at>
References: <E1JPuyj-0007vC-6J@smartmx-12.inode.at>
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.dehmer@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>
<code>
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)
</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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Bart.Degryse@indicator.be
Subject: Re: inhibit rounding on numeric columns
In-Reply-To: <47B55321.A3DD.0030.0@indicator.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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