Re: Comparing fixed precision to floating

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(dot)finn(at)tacticalExecutive(dot)com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>