pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinhibit rounding on numeric columns
4+ messages / 4 participants
[nested] [flat]
* inhibit rounding on numeric columns
@ 2008-02-15 07:28 Horst Dehmer <horst.dehmer@inode.at>
2008-02-15 07:42 ` Re: inhibit rounding on numeric columns Scott Marlowe <scott.marlowe@gmail.com>
2008-02-15 07:46 ` Re: inhibit rounding on numeric columns Niklas Johansson <spot@tele2.se>
2008-02-15 07:53 ` Re: inhibit rounding on numeric columns Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 3 replies; 4+ messages in thread
From: Horst Dehmer @ 2008-02-15 07:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: inhibit rounding on numeric columns
2008-02-15 07:28 inhibit rounding on numeric columns Horst Dehmer <horst.dehmer@inode.at>
@ 2008-02-15 07:42 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Scott Marlowe @ 2008-02-15 07:42 UTC (permalink / raw)
To: Horst Dehmer <horst.dehmer@inode.at>; +Cc: pgsql-sql
On Fri, Feb 15, 2008 at 1:28 AM, Horst Dehmer <horst.dehmer@inode.at> wrote:
>
>
> 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?
I can't think of an easy way. But you can always make your own type I guess.
> Sadly, changing the schema is hardly a possibility for me :-(
Sadly, the people who have burdened you with this problem don't seem
to have fully understood the implications of rounding and the SQL
specification's take on when it happens.
Can you not even just make it wider? I mean, pgsql can change types
on the fly, and since the value's will fit in newer, wider numerics,
then you have an easy solution.
Anything else you do is going to far more invasive than changing the schema.
Or you'll have to handle all this in code somewhere.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: inhibit rounding on numeric columns
2008-02-15 07:28 inhibit rounding on numeric columns Horst Dehmer <horst.dehmer@inode.at>
@ 2008-02-15 07:46 ` Niklas Johansson <spot@tele2.se>
2 siblings, 0 replies; 4+ messages in thread
From: Niklas Johansson @ 2008-02-15 07:46 UTC (permalink / raw)
To: Horst Dehmer <horst.dehmer@inode.at>; +Cc: pgsql-sql
On 15 feb 2008, at 08.28, Horst Dehmer wrote:
> 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?
> 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
> 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.
I don't know a way to achieve what you ask for, and I'm not sure I
think there should be. Have you thought about the consequences of
treating 3.1415 as being different from 3.1415000? If you do a
SELECT 3.1415 = 3.1415000 it will always evaluate to true, which it
should, no matter to which precision you cast the operands. Numeric
scale is not the equivalent of character string length.
What is the actual problem you're trying to solve?
Sincerely,
Niklas Johansson
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: inhibit rounding on numeric columns
2008-02-15 07:28 inhibit rounding on numeric columns Horst Dehmer <horst.dehmer@inode.at>
@ 2008-02-15 07:53 ` Bart Degryse <Bart.Degryse@indicator.be>
2 siblings, 0 replies; 4+ messages in thread
From: Bart Degryse @ 2008-02-15 07:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-02-15 07:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-15 07:28 inhibit rounding on numeric columns Horst Dehmer <horst.dehmer@inode.at>
2008-02-15 07:42 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-15 07:46 ` Niklas Johansson <spot@tele2.se>
2008-02-15 07:53 ` Bart Degryse <Bart.Degryse@indicator.be>
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