Thread: problem with numeric

v6.5.3

While testing the new ODBC driver I ran into the following problem with
queries on numeric fields. Unless the values are surrounded by single quote
marks, the query fails with the message: Unable to identify an operator '='
for types 'numeric' and 'float'. This occurs in the WHERE clause of the
query. 

SELECT * from tbl WHERE numfld = 99.125;
UPDATE tbl SET numfld = 99.13 WHERE numfld = 99.125;

An INSERT works fine.

These statements execute ok on float fields, but not numeric. This happens
via ODBC and also with psql from a Linux console.

Ken

"Ken J. Wright" wrote:
> 
> v6.5.3
> 
> While testing the new ODBC driver I ran into the following problem with
> queries on numeric fields. Unless the values are surrounded by single quote
> marks, the query fails with the message: Unable to identify an operator '='
> for types 'numeric' and 'float'. This occurs in the WHERE clause of the
> query.
> 
> SELECT * from tbl WHERE numfld = 99.125;
> UPDATE tbl SET numfld = 99.13 WHERE numfld = 99.125;
> 
> An INSERT works fine.
> 
> These statements execute ok on float fields, but not numeric. This happens
> via ODBC and also with psql from a Linux console.
> 
> Ken
> 
> ************

Ok, I can put quotes around it, as long as you use a bound parameter. 
In other words, you have to use something like "select * from tbl where
numfld = ?", and then bind parameter 1 to be the numeric type.

The driver will not try to put quotes around a numeric embedded into an
sql query.

For completeness, I think Postgres should work with or without the
quotes, after all, it is a numeric field.   If this where the case, then
flat queries (i.e., no Bounded parameters) would work too.  

You might want to post this to the hackers list too.

Byron

Byron

"Ken J. Wright" wrote:
> 
> v6.5.3
> 
> While testing the new ODBC driver I ran into the following problem with
> queries on numeric fields. Unless the values are surrounded by single quote
> marks, the query fails with the message: Unable to identify an operator '='
> for types 'numeric' and 'float'. This occurs in the WHERE clause of the
> query.
> 
> SELECT * from tbl WHERE numfld = 99.125;
> UPDATE tbl SET numfld = 99.13 WHERE numfld = 99.125;
> 
> An INSERT works fine.
> 
> These statements execute ok on float fields, but not numeric. This happens
> via ODBC and also with psql from a Linux console.
> 
> Ken
> 
> ************


An updated odbc driver is available that fixes this at:

ftp://ftp.postgresql.org/pub/odbc/index.html

Byron

"Ken J. Wright" <ken@ori-ind.com> writes:
> While testing the new ODBC driver I ran into the following problem with
> queries on numeric fields. Unless the values are surrounded by single quote
> marks, the query fails with the message: Unable to identify an operator '='
> for types 'numeric' and 'float'. This occurs in the WHERE clause of the
> query. 
>
> SELECT * from tbl WHERE numfld = 99.125;
> UPDATE tbl SET numfld = 99.13 WHERE numfld = 99.125;

This is indubitably a backend bug.  It's still there in current sources,
too.  Possibly related to an item I have on my personal TODO list:

: Why is NUMERIC not considered numeric by TypeCategory()?  Why is DATE not
: considered a datetime type?  Isn't this routine fundamentally broken?
: If we need a category it should be taken from a pg_type field, not
: hardwired in code...

Thomas, what say you?
        regards, tom lane

> : Why is NUMERIC not considered numeric by TypeCategory()?  Why is DATE not
> : considered a datetime type?  Isn't this routine fundamentally broken?
> : If we need a category it should be taken from a pg_type field, not
> : hardwired in code...
> Thomas, what say you?

Not sure why DATE is not a date/time type. NUMERIC is not included for
two reasons: it is newer than the other code, and I'm not certain
where it should appear in a "promotion heirarchy" due to its
performance.

But in general you are right. I did the implementation using hardcoded
info, with the expectation that the eventual "right answer" would
involve allowing the type equivalence *and* type promotion info to be
stored in a table. For one thing, that is the only way afaik to get
user-defined types to participate in this implicit type coersion
scheme.

But at the time I didn't want to take the leap to defining a new table
or adding a column(s) to pg_type, until the current scheme was in the
field for a while and others had a chance to exercise it and
contribute ideas. 

I was assuming that a new table would be required, rather than using
pg_type, but maybe we only need two columns in a one-to-one
relationship and if so then pg_type might suffice. Are new columns
"typecategory" and "promotiontype" sufficient??
                   - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

At 22:29 02/01/2000 -0500, Byron Nikolaidis wrote:
>
>
>"Ken J. Wright" wrote:
>> 
>> v6.5.3
>> 
>> While testing the new ODBC driver I ran into the following problem with
>> queries on numeric fields. Unless the values are surrounded by single quote
>> marks, the query fails with the message: Unable to identify an operator '='
>> for types 'numeric' and 'float'. This occurs in the WHERE clause of the
>> query.
>> 
>> SELECT * from tbl WHERE numfld = 99.125;
>> UPDATE tbl SET numfld = 99.13 WHERE numfld = 99.125;
>> 
>> An INSERT works fine.
>> 
>> These statements execute ok on float fields, but not numeric. This happens
>> via ODBC and also with psql from a Linux console.
>> 
>> Ken
>> 
>> ************
>
>
>An updated odbc driver is available that fixes this at:
>
>ftp://ftp.postgresql.org/pub/odbc/index.html

Yes Byron, both Access & the BDE like this ;-)

Thanks,

Ken