Postgres Pro
Downloads
Home   >   mailing lists

Thread: precision of numeric type

precision of numeric type

From
"w.winter"
Date:
Hi,

on testing our auto-configuration persistence framework ACP against
PostgreSQL we found the following problem:

I have a table with a column of type numeric(48,10). The PostgreSQL docs
say: "The type numeric can store numbers of practically unlimited size and
precision, while being able to store all numbers and carry out all
calculations exactly."

I store the Double value 1212121234.5634349876 with a
PreparedStatement.setDouble()

I make a PreparedStatement with SELECT FROM TESTTABLE WHERE TESTCOLUMN=?
and set the Parameter with
stmt.setDouble(1, new Double(1212121234.56343498));

The row will be found!

The same happens when I do
stmt.setString(1, String.valueOf(new Double(1212121234.56343498)));

and if I do
stmt.setDouble(1, new Double(1212121234.5634349));
an error is produced : Unable to identify an operator '=' for types
'numeric' and 'double precision'

it seems to me, the behaviour with numeric datatypes is not consistent.

Wolfgang

___________________
Dr. Wolfgang Winter
LogiTags Systems
www.logitags.com

Re: precision of numeric type

From
Bruce Momjian
Date:
I am not sure.  Others may have a better answer, but the problem may be
due to comparing a non-exact type like numberic to an inexact type like
double.  You may find 7.3beta has this fixed because we did work on
improving such casts in that release.

---------------------------------------------------------------------------

w.winter wrote:
> Hi,
>
> on testing our auto-configuration persistence framework ACP against
> PostgreSQL we found the following problem:
>
> I have a table with a column of type numeric(48,10). The PostgreSQL docs
> say: "The type numeric can store numbers of practically unlimited size and
> precision, while being able to store all numbers and carry out all
> calculations exactly."
>
> I store the Double value 1212121234.5634349876 with a
> PreparedStatement.setDouble()
>
> I make a PreparedStatement with SELECT FROM TESTTABLE WHERE TESTCOLUMN=?
> and set the Parameter with
> stmt.setDouble(1, new Double(1212121234.56343498));
>
> The row will be found!
>
> The same happens when I do
> stmt.setString(1, String.valueOf(new Double(1212121234.56343498)));
>
> and if I do
> stmt.setDouble(1, new Double(1212121234.5634349));
> an error is produced : Unable to identify an operator '=' for types
> 'numeric' and 'double precision'
>
> it seems to me, the behaviour with numeric datatypes is not consistent.
>
> Wolfgang
>
> ___________________
> Dr. Wolfgang Winter
> LogiTags Systems
> www.logitags.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: precision of numeric type

From
snpe
Date:
You get BigDecimal, not Double
regards
Haris Peco
On Sunday 27 October 2002 03:36 pm, Bruce Momjian wrote:
> I am not sure.  Others may have a better answer, but the problem may be
> due to comparing a non-exact type like numberic to an inexact type like
> double.  You may find 7.3beta has this fixed because we did work on
> improving such casts in that release.
>
> ---------------------------------------------------------------------------
>
> w.winter wrote:
> > Hi,
> >
> > on testing our auto-configuration persistence framework ACP against
> > PostgreSQL we found the following problem:
> >
> > I have a table with a column of type numeric(48,10). The PostgreSQL docs
> > say: "The type numeric can store numbers of practically unlimited size
> > and precision, while being able to store all numbers and carry out all
> > calculations exactly."
> >
> > I store the Double value 1212121234.5634349876 with a
> > PreparedStatement.setDouble()
> >
> > I make a PreparedStatement with SELECT FROM TESTTABLE WHERE TESTCOLUMN=?
> > and set the Parameter with
> > stmt.setDouble(1, new Double(1212121234.56343498));
> >
> > The row will be found!
> >
> > The same happens when I do
> > stmt.setString(1, String.valueOf(new Double(1212121234.56343498)));
> >
> > and if I do
> > stmt.setDouble(1, new Double(1212121234.5634349));
> > an error is produced : Unable to identify an operator '=' for types
> > 'numeric' and 'double precision'
> >
> > it seems to me, the behaviour with numeric datatypes is not consistent.
> >
> > Wolfgang
> >
> > ___________________
> > Dr. Wolfgang Winter
> > LogiTags Systems
> > www.logitags.com
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: precision of numeric type

From
Barry Lind
Date:
Wolfgang,


w.winter wrote:
> Hi,
>
> on testing our auto-configuration persistence framework ACP against
> PostgreSQL we found the following problem:
>
> I have a table with a column of type numeric(48,10). The PostgreSQL docs
> say: "The type numeric can store numbers of practically unlimited size and
> precision, while being able to store all numbers and carry out all
> calculations exactly."

This is true.

>
> I store the Double value 1212121234.5634349876 with a
> PreparedStatement.setDouble()
>

But the problem is that java Double isn't able to perform calculations
exactly.  If you want exact precision you should be using the BigDecimal
datatype (and getBigDecimal/setBigDecimal).  BigDecimal is the logical
java data type that corresponds to the numeric type in postgres.

thanks,
--Barry

Re: precision of numeric type

From
"w.winter"
Date:
----- Original Message -----
From: "Barry Lind" <blind@xythos.com>
To: "w.winter" <w.winter@logitags.com>
Cc: <pgsql-jdbc@postgresql.org>
Sent: Tuesday, October 29, 2002 5:53 PM
Subject: Re: [JDBC] precision of numeric type


> >
> > I store the Double value 1212121234.5634349876 with a
> > PreparedStatement.setDouble()
> >
>
> But the problem is that java Double isn't able to perform calculations
> exactly.  If you want exact precision you should be using the BigDecimal
> datatype (and getBigDecimal/setBigDecimal).  BigDecimal is the logical
> java data type that corresponds to the numeric type in postgres.
>

Hi Barry,

you are right, that was my error. I will use BigDecimal in this case.

thank you for the hint
Wolfgang