From: Horst Dehmer <horst.dehmer@inode.at>
To: pgsql-sql@postgresql.org
Subject: inhibit rounding on numeric columns
Date: Fri, 15 Feb 2008 08:28:41 +0100
Message-ID: <E1JPuyj-0007vC-6J@smartmx-12.inode.at> (raw)
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: horst.dehmer@inode.at
Subject: Re: inhibit rounding on numeric columns
In-Reply-To: <E1JPuyj-0007vC-6J@smartmx-12.inode.at>
* 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