Inconsistent or incomplete behavior obverse in where clause - Mailing list pgsql-sql

Hello,
Our application development group has observed what we 
feel is inconsistent behavior when comparing numeric 
column references to constant/literal values in SQL.  

I would appreciate comments on the best approach to 
this problem that will allow for the highest
portability of our application code.  I have searched
the archives and online docs, but so far have not found 
anyone addressing the problem quite this way.

Assume wuActive is a numeric field ( with scale but no
precision ) in the table WU: select count(wuid) from WU where wuActive = 0 --works fine select count(wuid) from WU
wherewuActive = '0' --works fine select count(wuid) from WU where wuActive = '0.0' --works fine select count(wuid) from
WUwhere wuActive = 0.0 --throws the 
 
following exception:

"Unable to identify an operator '=' for types 'numeric' and 'double
precision' You will have to retype this query using an explicit cast"

Second, assume tPct is a numeric field ( having scale of 4 and
precision of 1 ) in the table T select count(tid) from T where tPct > 77 --works fine select count(tid) from T where
tPct> '77' --works fine select count(tid) from T where tPct > '77.5' --works fine select count(tid) from T where tPct >
77.5-- again throws 
 
the exception:

"Unable to identify an operator '>' for types 'numeric' and 'double
precision' You will have to retype this query using an explicit cast"

This seems to occur regardless of connectivity drivers used 
(ODBC, JDBC, etc..)

I am aware of the use of type casting to force the desired 
behavior in these situations.  I have also started to go down 
the road of creating functions and operators to force numeric 
to numeric comparison operations when comparing numeric to float, 
but realize that this approach is fraught with pitfalls, in fact 
it is interesting to us to note that with an operator in place 
to force numeric = float comparisons to parse as numeric = numeric, 
we started getting the opposite behavior.  Queries with 'column 
reference' = 0.0 worked fine, but queries with 'column reference' = 0 
threw a variant of the previous exception:

"Unable to identify an operator '=' for types 'numeric' and 'integer'"

Overall, this behavior appears to be inconsistent and is not 
the same behavior I have experienced with many other DBMS's.
Specifically, it seems strange that the parser does not treat 
values 0.0 or 77.5 as numeric(s[,p]) when comparing the values
to a column reference known to be of type numeric (s,[p]).  

Is an unquoted number in the form of NN.N always treated as a 
float?  If the planner could somehow recognize that the constant/
literal value was being compared to a column reference of the
type numeric (s,p) and treat the value accordingly, then would
operator identification no longer be a problem?

We are looking to maintain a high degree of portability in our 
application code, and while "CAST ( expression as type )" is 
fairly portable, no one here feels that it is a portable as
column reference = literal/constant value.   If someone knows
of a better approach, or can point us to documentation of build or
run-time configuration that affects the query planner where this 
issue is concerned, it would be much appreciated.

Thanks,

Paul Ogden
Database Administrator/Programmer
Claresco Corporation
(510) 549-2290