Thread: Correction: Working on "SELECT * WHERE numeric_col = 2001.2" problem?

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:

> 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