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