Working on "SELECT * WHERE numeric_col = 2001.2" problem?
Over the past year I have been using MySQL to serve as the backend for our
WIN32 ODBC client. After reading several articles regarding PostgreSQL, I
decided to give it a try, hoping that by moving our data over to pgsql I
could gain some additional features (primarily COMMIT and ROLLBACK)...
Converting the WIN32 client has gone faily well with the exception of one,
perhaps two problems.
First of all, here's a brief breakdown of the box I'm running the SQL server
on:
i686 Machine
RH Linux 7.0 2.2.16-22
PostgreSQL v7.1.3
There are times where the client makes queries to the server to the effect
of:
"SELECT * FROM cyear WHERE year=2001.5"
but is given an error stating that the '-' operator is unidentified for
numeric and float8 types.
After receiving this error, I checked the doc/TODO file and found that the
deficiency was known...\
Is this an issue that is to be addressed in later (hopefully in the near
future?) revisions of pgsql, or is it a relatively low priority?
This feature is absolutely critical for the functionality of our program, if
a reasonable workaround/fix isn't available I'll have to continue with
MySQL.
Thanks,
Bob
I would like to correct a misprint on my original post. Earlier I stated:
... an error stating that the '-' operator is unidentified for numeric and
float8 types....
The "unidentified operator" should be the "=" operator (as shown correctly
in the SQL query example of my original post), not the "-" operator as
previously mentioned.
My apologies,
Bob
On Thu, 13 Dec 2001, Bob Jones wrote:
I would like to correct a misprint on my original post. Earlier I stated:
... an error stating that the '-' operator is unidentified for numeric and
float8 types....The "unidentified operator" should be the "=" operator (as shown correctly
in the SQL query example of my original post), not the "-" operator as
previously mentioned.
Well, the current easiest way to get around it is to either explicitly
cast the constant to numeric or single quote it (otherwise it's treated
as float8). Although you could get rid of the error by defining the
functions and making the operators, I'm not sure what'd be involved in
getting index scans to work properly.
There's been talk about working on the type system stuff for int, float,
numeric, etc, but I don't think a concensus was reached on what should be
done (you can check the hackers archives).
That sort of query works fine on my install, Pg 7.2b3. If you're having
problems with the conversion, you could try using the round function on the
float to get it down to zero decimal places thusly:
select * from whatever where numeric_col = round(2001.2);
or you would try casting numeric_col like so:
select * from whatever where numeric_col::float = 2001.1;
J
Bob Jones wrote:
Show quoted text
I would like to correct a misprint on my original post. Earlier I stated:
... an error stating that the '-' operator is unidentified for numeric and
float8 types....The "unidentified operator" should be the "=" operator (as shown correctly
in the SQL query example of my original post), not the "-" operator as
previously mentioned.My apologies,
Bob