pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with numeric
7+ messages / 4 participants
[nested] [flat]
* problem with numeric
@ 2000-02-01 17:57 Ken J. Wright <ken@ori-ind.com>
0 siblings, 4 replies; 7+ messages in thread
From: Ken J. Wright @ 2000-02-01 17:57 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] problem with numeric
@ 2000-02-02 01:52 Byron Nikolaidis <byron.nikolaidis@home.com>
parent: Ken J. Wright <ken@ori-ind.com>
3 siblings, 0 replies; 7+ messages in thread
From: Byron Nikolaidis @ 2000-02-02 01:52 UTC (permalink / raw)
To: Ken J. Wright <ken@ori-ind.com>; +Cc: pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] problem with numeric
@ 2000-02-02 03:29 Byron Nikolaidis <byron.nikolaidis@home.com>
parent: Ken J. Wright <ken@ori-ind.com>
3 siblings, 0 replies; 7+ messages in thread
From: Byron Nikolaidis @ 2000-02-02 03:29 UTC (permalink / raw)
To: Ken J. Wright <ken@ori-ind.com>; +Cc: pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] problem with numeric
@ 2000-02-02 04:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ken J. Wright <ken@ori-ind.com>
3 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2000-02-02 04:15 UTC (permalink / raw)
To: Ken J. Wright <ken@ori-ind.com>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] problem with numeric
@ 2000-02-02 05:51 Ken J. Wright <ken@ori-ind.com>
0 siblings, 0 replies; 7+ messages in thread
From: Ken J. Wright @ 2000-02-02 05:51 UTC (permalink / raw)
To: Byron Nikolaidis <byron.nikolaidis@home.com>; +Cc: pgsql-interfaces
At 20:52 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
>>
>> ************
>
>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.
Yeah, I think the backend kinda goes for a Burton here. Like you say, it is
a numeric field!
Ken
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] problem with numeric
@ 2000-02-02 14:30 Thomas Lockhart <lockhart@alumni.caltech.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Thomas Lockhart @ 2000-02-02 14:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ken J. Wright <ken@ori-ind.com>; pgsql-interfaces
> : 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] problem with numeric
@ 2000-02-02 15:50 Ken J. Wright <ken@ori-ind.com>
parent: Ken J. Wright <ken@ori-ind.com>
3 siblings, 0 replies; 7+ messages in thread
From: Ken J. Wright @ 2000-02-02 15:50 UTC (permalink / raw)
To: Byron Nikolaidis <byron.nikolaidis@home.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2000-02-02 15:50 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-01 17:57 problem with numeric Ken J. Wright <ken@ori-ind.com>
2000-02-02 01:52 ` Byron Nikolaidis <byron.nikolaidis@home.com>
2000-02-02 03:29 ` Byron Nikolaidis <byron.nikolaidis@home.com>
2000-02-02 04:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-02 14:30 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2000-02-02 15:50 ` Ken J. Wright <ken@ori-ind.com>
2000-02-02 05:51 Re: [INTERFACES] problem with numeric Ken J. Wright <ken@ori-ind.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox