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