Thread: Comparing fixed precision to floating
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
FYI -- JDBC questions should go to the pgsql-jdbc mail list. As for your problem, I think probably the easiest workaround is to explicitly cast your constants. Assuming you are using PreparedStatements, a statement of the following form should work: select * from foo where bar = ?::numeric When the parameter is bound, the resulting statement sent to the server will be: select * from foo where bar = 123.456::numeric which should work correctly. thanks, --Barry Mike Finn wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
On Tue, 21 Aug 2001 15:41:22 -0600, Mike Finn wrote: >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 I have the same problem but nobody seems to understand that you can't always change the query like in my case, where a comercial app. sends the query. I tried the following with no avail. create function numeric_eq(numeric,float8) returns bool as '' language 'internal'; create operator = ( leftarg=numeric, rightarg=float8, procedure=numeric_eq, commutator='=', negator='<>', restrict=eqsel, join=eqjoinsel ); The Problem is: It doesn't work and the backend process crashes and get's restarted. I tried it with 7.03 and I guess 7.1.3 behaves the same. Do you have any ideas? I agree, that should work in postresql. regards Johann Johann Zuschlag zuschlag@online.de
Johann Zuschlag wrote:
> I have the same problem but nobody seems to understand
> that you can't always change the query like in my case,
I understand :-).
My problem occurs for pretty much the same reason, and the suggestion I put
forth on the interfaces.java list is that either the JDBC driver should
understand that an explicit cast is required when substituting the constant
values (much like the way it understands strings and quotes), or that the
back-end should have a commutable =(numeric, float8).
Your routine is not working becase you have no body in your function.
The HACK below does work. I say HACK because
a) this routine should really should be in C
as it will executed billions of times when
used in where clauses (but I am not a "C" programmer)
b) I don't really understand the effects/implications
of the commutator, negator, restrict, and join parameters
(I simply copied them from your example, and the docs
were a bit beyond me)
Maybe someone listening with a bit more pg savvy can help us out further.
P.S. IMHO there should also be "standard" commutable operators for
=(numeric, int8)
=(numeric, int4)
etc...
========================================
Tried on pg7.1, Linux
create function numeric_eq(numeric,float8) returns bool as '
select $1 = $2::numeric;
' language 'sql';
drop operator = (numeric, float8);
create operator = (
leftarg=numeric,
rightarg=float8,
procedure=numeric_eq,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);
=============================
Mike Finn
Tactical Executive Systems
mike.finn@tacticalExecutive.com
Hi Mike On Thu, 23 Aug 2001 09:08:39 -0600, Mike Finn wrote: >Your routine is not working becase you have no body in your function. Well done! It now works. I didn't need to drop the operator, since there was nothing to drop. :-) > >The HACK below does work. I say HACK because >a) this routine should really should be in C > as it will executed billions of times when > used in where clauses (but I am not a "C" programmer) It's not a hack and it works fine with more than 10.000 datasets. But maybe C would be faster. >b) I don't really understand the effects/implications > of the commutator, negator, restrict, and join parameters > (I simply copied them from your example, and the docs > were a bit beyond me) > Just read the manual. The above is explained in detail. >Maybe someone listening with a bit more pg savvy can help us out further. Maybe :-) regards Johann Johann Zuschlag zuschlag@online.de