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