i have a problem in a table with the following columns:
...
probability | double precision |
unitcost | numeric(15,2) |
...
when i try and do the following sql:
select (unitcost*probability) from oppproducttype
i get the following error:
ERROR: Unable to identify an operator '*' for types 'numeric' and
'float8' You will have to retype this query using an explicit cast
is this kind of thing a regular thing for postgresql? it seems that to
postgres, everything (even operators) are methods that take (and only
take) declared parameters, and postgres also seems strangely shy at
doing relatively straightforward conversions. how does one handle this?
we are getting our product running on postgresql ( running very well
indeed btw, congratulations ) and can't really afford to ship our own
version of postgresql that has the required parameterized operator
methods installed. nor is it possible (or realistic really) to manually
cast these things. all the sql is intentionally generic and runs on
many different databases, as well as all the sql being dynamically
generated. figuring out when postgresql is going to have a problem
doing the math would be difficult.
the only solution i can think of, if it is possible, is to have a script
that the client would need to run to declare the missing parameter
combinations for the standard operators. this isn't very desireable,
but it is the only real solution i can see so far. any other
suggestions?
why does this happen? i have run into this before, and was able to
perform the explicit cast (the code was postgresql only), but it seems
like this is always going to be a problem, unless i'm missing something.
are there plans for more thorough dynamic-casting logic, or does one
just need to go through all the combinations of datatypes and make sure
that every combination is entered in the operator mapping tables? i
guess it would need to be entered both ways also ( int*float8,
float8*int )?
explanations and/or suggestions greatly appreciated.
--
CraigL->Thx(); Be Developer ID: 5852