Обсуждение: precision of numeric type
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
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
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)
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
----- 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