Working on "SELECT * WHERE numeric_col = 2001.2" problem?

Started by Bob Jonesover 24 years ago2 messagesgeneral
Jump to latest
#1Bob Jones
bjones@autoresourcesinc.com

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 fairly 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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bob Jones (#1)
Re: Working on "SELECT * WHERE numeric_col = 2001.2" problem?

"Bob Jones" <bjones@autoresourcesinc.com> writes:

... 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?

It's not so much that it's low priority as that a non-hack solution
requires some rather subtle revisions of Postgres' type system, and
there's not yet agreement on how to do that. (Look through the
pghackers list archives if you want to see the previous discussions.)

Quick-hack solutions include (a) persuading your client program to
put single-quotes around the literal, or (b) defining your own
numeric-vs-float8-equals operator. AFAIK (b) would not support the
use of indexes on the numeric column, so it's not a very good solution,
but it might do as a stopgap.

regards, tom lane