I am using numeric(p,s) fields in a database schema.
Using queries that contain a comparison like
...
where numericField = 456.789
....
will generate an error
Unable to identify an operator '=' for types 'numeric' and 'float8'
You will have to retype this query using an explicit cast
and if i explicitly cast the 456.789 (456.789::numeric) it does in fact work.
But how do we get around this error when using JDBC?
Shouldn't =(numeric, float8) be a standard operator in postgresql?
My query is a dynamically prepared statement in java where many of the
constant values are user supplied and poped into the statement via
pstmt.setObject(pos, valueObj, type)
in the case of a numeric field the "type" parameter is Types.NUMERIC and the
"valueObj" parameter is a java.math.BigDecimal. (java.math.BigDecimal is the
only way I know of to represent fixed precision and scale number in java).
And of course this will blow with the previous error.
I do have a work around which is to force the user supplied constant (a
BigDecimal) to a string and user pstmt.setString(...). Effectively this
create a clause of the form
...
where numericField = '456.789'
....
but it postgres will automatically cast the right hand side to a numeric I
would have expected it to be able to cast a float8 constant to a numeric as
well.
If there is good reason why this can't be done, could someone explain what I
am missing. Else could we put a =(numeric, float8) operator on the todo list?
Thanks, in advance for any help.
Mike.
===================
Mike Finn
Tactical Executive Systems
mike.finn@tacticalExecutive.com
