Thread: Casting numeric values to double
Hi,
I recently stumbled uppon the following thing:
If I compare a field of type "numeric" to a small decimal constant like
"3.6", the constant is interpreted as a "double precision" value and the
comparison fails with the following message:
# SELECT * FROM tr_table_okpx WHERE prating >= 3.6;
Unable to identify an operator '>=' for types 'double precision' and
'numeric'
You will have to retype this query using an explicit cast
I then changed the field to be of type "double precision". Now if I compare
it to a constant with more than 15 decimal digits (like
"3.5999999999999996"), the constant is interpreted as numeric value and the
comparison fails again.
# SELECT * FROM tr_table_okpx WHERE prating >= 3.5999999999999996;
Unable to identify an operator '>=' for types 'double precision' and
'numeric'
You will have to retype this query using an explicit cast
This is a little annoying, as I am generating my queries in Java where
double values can easily take more than 15 digits.
I'm using Postgre 7.2, which might be a bit outdated but I could not find
any information whether this behaviour has changed in newer versions. So
before I try to upgrade I'd like to ask if upgrading will help or if the
recommended way to handle this is really cast all constants:
# SELECT * FROM tr_table_okpx WHERE prating >= 3.5999999999999996::double
precision;
Any help appreciated.
-- Daniel
--
http://www.despite.ch/ -- http://www.npfdd.ch/
5 GB Mailbox, 50 FreeSMS http://www.gmx.net/de/go/promail
+++ GMX - die erste Adresse f�r Mail, Message, More +++
"Daniel Schregenberger" <npfdd@gmx.net> writes:
> If I compare a field of type "numeric" to a small decimal constant like
> "3.6", the constant is interpreted as a "double precision" value and the
> comparison fails with the following message:
> # SELECT * FROM tr_table_okpx WHERE prating >= 3.6;
> Unable to identify an operator '>=' for types 'double precision' and
> 'numeric'
> You will have to retype this query using an explicit cast
> I'm using Postgre 7.2, which might be a bit outdated but I could not find
> any information whether this behaviour has changed in newer versions.
More than "a bit" outdated, and yes it has.
regards, tom lane
On Mon, Oct 03, 2005 at 03:31:42PM +0200, Daniel Schregenberger wrote: > Hi, > > I recently stumbled uppon the following thing: > If I compare a field of type "numeric" to a small decimal constant like > "3.6", the constant is interpreted as a "double precision" value and the > comparison fails with the following message: > > # SELECT * FROM tr_table_okpx WHERE prating >= 3.6; > > Unable to identify an operator '>=' for types 'double precision' and > 'numeric' > You will have to retype this query using an explicit cast If at all possible, can the queries be arranged to output single quotes around the numbers? If you do that, the choice of type will be deferred until the point where it knows what "prating" is. > I'm using Postgre 7.2, which might be a bit outdated but I could not find > any information whether this behaviour has changed in newer versions. So Yeah, it's better in later versions. Either explicitly typing (with ::numeric) or explicitly untyping (with single quotes) will work. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Mon, 2005-10-03 at 17:44 +0200, Martijn van Oosterhout wrote: > On Mon, Oct 03, 2005 at 03:31:42PM +0200, Daniel Schregenberger wrote: > > I recently stumbled uppon the following thing: > > If I compare a field of type "numeric" to a small decimal constant like > > "3.6", the constant is interpreted as a "double precision" value and the > > comparison fails with the following message: > > > > # SELECT * FROM tr_table_okpx WHERE prating >= 3.6; > > > > Unable to identify an operator '>=' for types 'double precision' and > > 'numeric' > > You will have to retype this query using an explicit cast > > If at all possible, can the queries be arranged to output single quotes > around the numbers? If you do that, the choice of type will be deferred > until the point where it knows what "prating" is. oh, I didn't think about untyping. I will try this as well, thanks! > > I'm using Postgre 7.2, which might be a bit outdated but I could not find > > any information whether this behaviour has changed in newer versions. So > > Yeah, it's better in later versions. Either explicitly typing (with > ::numeric) or explicitly untyping (with single quotes) will work. well, explicitly typing works in 7.2, I just wondered if that's the way to go or if newer versions define implicit casts like for example C/C++ does: one operand is double -> the other has to be double too. As far as I understood it now, it has changed and I will try to upgrade. Thanks for your help! (Tom as well) -- Daniel -- http://www.despite.ch/ -- http://www.npfdd.ch/ Make sure your E-mail can be read by everyone! http://www.betips.net/etc/evilmail.html Please avoid sending me Word or PowerPoint attachments. http://www.gnu.org/philosophy/no-word-attachments.html -- You will obey or molten silver will be poured into your ears.