Thread: casts and conversions
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
Craig Longman <craigl@begeek.com> writes:
> select (unitcost*probability) from oppproducttype
> 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?
The problem here is that we use a very generic, datatype-independent
algorithm for resolving operator type ambiguities. It's nice and
extensible, which is great for user-defined datatypes ... but there's
really no way to handle all the standard numeric datatypes in an
intuitive fashion without introducing type-specific knowledge. We've
had discussions about fixing this in the past (see e.g. pghackers
archives from last May & June), but we've not yet come up with a
solution that satisfies everyone. It's still on the to-do list though.
regards, tom lane
On Sat, Jun 16, 2001 at 01:07:29AM -0400, Craig Longman wrote: <problem wiuth no automatic float8<->numeric casts> Tom Lane has already addressed the 'why is it this way' question. I'll address your proposed work arounds. > > 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? Yup, that's pretty much the only way to do this. Either run scripts as part of the install, or (if there's a logical place for it in the workflow) you could much around to see if the declarations are already there, and only run the script if their missing. > > 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 Check the HACKERS archives: there've been _long_ discussions about how to do dynimic casting correctly, in the face of a user extensible type system. Not trivial, at all. > 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 )? I'd test with all the combinations of types you need, then only add enough new mappings to get everything working. Ross
On 17 Jun 2001 21:02:03 -0500, Ross J. Reedstrom wrote: > On Sat, Jun 16, 2001 at 01:07:29AM -0400, Craig Longman wrote: > > <problem wiuth no automatic float8<->numeric casts> > > Tom Lane has already addressed the 'why is it this way' question. I'll > address your proposed work arounds. he did. i tried looking in pg-hackers for most of 2000, but was unable to find the thread(s). i'll keep looking though. so, i presume then that this is a problem in the database because no-one has sat down and made sure that all the possible combinations of built-in datatypes are handled for all the various built-in functions/operators? or is there another reason for it? perhaps this is something where i can help then. > > 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? > > Yup, that's pretty much the only way to do this. Either run scripts as > part of the install, or (if there's a logical place for it in the workflow) > you could much around to see if the declarations are already there, and only > run the script if their missing. > > > 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 )? > > I'd test with all the combinations of types you need, then only add > enough new mappings to get everything working. this statement makes we think that there is some other reason as to why ALL the basic mappings weren't included. does it just slow things down too much? finally, can you point me to where i can find out HOW to do this? i hope there isn't actual C code to write, but i suspect that there might be. as i said previously, if this is simply a case of setting up all the mappings being a low-priority thing, then i would be happy (?!) to offer some time to sort that out once and for all. thanks! -- CraigL->Thx(); Be Developer ID: 5852 Check out <http://www.begeek.com/>!
Craig Longman <craigl@begeek.com> writes:
> so, i presume then that this is a problem in the database because no-one
> has sat down and made sure that all the possible combinations of
> built-in datatypes are handled for all the various built-in
> functions/operators?
No, that's not it, and that would be quite the wrong way to go about it.
We have, hmm, six different built-in numeric datatypes (int2, int4,
int8, float4, float8, numeric; not to count quasi-numerics like OID,
XID, "char", etc). Does it make sense to field thirty-six variants of
"plus", thirty-six of "minus", etc? How many would you need to add to
support even one additional user-defined numeric type ("complex", say)?
Nope, it just doesn't scale.
What we need is some logic that decides on a common datatype to promote
the two inputs to and then apply a single-data-type operator. The
mechanics are there to do this, what we haven't got is the rule that
allows a unique choice to be made when there are several possibilities.
For example, in your float8 * numeric case, the system hasn't got a way
to decide between using float8 multiply or numeric multiply, although it
can do either one if you coax it by supplying a cast. Interestingly,
it can do all the other cases, such as int4 * float8, just fine --- the
problem is that both float8 and numeric are marked as "preferred types",
leaving the poor thing with no way to make a choice. The real issue
here is that the "preferred type" heuristic doesn't encode enough
knowledge to deal with all the numeric datatypes. We need a more
general approach.
You can find more about this in the pghackers archives, eg thread
"type conversion discussion" around 5/14/2000.
regards, tom lane
Tom, Chris, > What we need is some logic that decides on a common datatype to > promote > the two inputs to and then apply a single-data-type operator. The > mechanics are there to do this, what we haven't got is the rule that > allows a unique choice to be made when there are several > possibilities. > For example, in your float8 * numeric case, the system hasn't got a > way > to decide between using float8 multiply or numeric multiply, although > it > can do either one if you coax it by supplying a cast. Interestingly, > it can do all the other cases, such as int4 * float8, just fine --- > the > problem is that both float8 and numeric are marked as "preferred > types", > leaving the poor thing with no way to make a choice. The real issue > here is that the "preferred type" heuristic doesn't encode enough > knowledge to deal with all the numeric datatypes. We need a more > general approach. And, just to throw a banana peel onto the sidewalk of argument, there is even a reason to keep things the way they are. If I:numeric * float8 And the database automatically decides that: numeric * float8 = float8 This can get me into trouble thanks to the .9999999999 's if I'm doing currency calculations, Likewise, if the database decides that: numeric * float8 = numeric This can get me into trouble due to rounding if I'm doing scientific calculations. Thus there's a good reason for the database to be indecisive about float/numeric decisions, and good reason for you to explicitly CAST. Or, as any VB programmer can tell you, sometimes implicit casts give you just enough rope to hang yourself. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"Josh Berkus" <josh@agliodbs.com> writes:
> And, just to throw a banana peel onto the sidewalk of argument, there is
> even a reason to keep things the way they are. [ ... ]
> Thus there's a good reason for the database to be indecisive about
> float/numeric decisions, and good reason for you to explicitly CAST.
Indeed. However, the SQL92 spec has its mind made up: section 6.12 says
1) If the data type of both operands of a dyadic arithmetic opera- tor is exact numeric, then the
datatype of the result is exact numeric, [ ... ]
2) If the data type of either operand of a dyadic arithmetic op- erator is approximate numeric, then
thedata type of the re- sult is approximate numeric.
So it's clear that for spec conformance we should cast numeric to float8
and do the multiply in float math. (Never mind that this could result
in loss of precision or even overflow :-( since PG's numerics actually
have a wider range of values than float8.)
A closely related issue is that SQL92 expects a literal like "1.234"
to be considered exact numeric, not approximate, so our initial typing
of such literals as float8 is wrong too. We'll have to fix literal
processing as well as the float8-vs-numeric-preference issue before we'll
have spec-compliant processing of expressions like "numericvar * 1.234".
(And yes, there's been lots and lots of discussion about how to do that,
too...)
regards, tom lane
This thread is not worth adding to TODO.detail, is it? > "Josh Berkus" <josh@agliodbs.com> writes: > > And, just to throw a banana peel onto the sidewalk of argument, there is > > even a reason to keep things the way they are. [ ... ] > > Thus there's a good reason for the database to be indecisive about > > float/numeric decisions, and good reason for you to explicitly CAST. > > Indeed. However, the SQL92 spec has its mind made up: section 6.12 says > > 1) If the data type of both operands of a dyadic arithmetic opera- > tor is exact numeric, then the data type of the result is exact > numeric, [ ... ] > > 2) If the data type of either operand of a dyadic arithmetic op- > erator is approximate numeric, then the data type of the re- > sult is approximate numeric. > > So it's clear that for spec conformance we should cast numeric to float8 > and do the multiply in float math. (Never mind that this could result > in loss of precision or even overflow :-( since PG's numerics actually > have a wider range of values than float8.) > > A closely related issue is that SQL92 expects a literal like "1.234" > to be considered exact numeric, not approximate, so our initial typing > of such literals as float8 is wrong too. We'll have to fix literal > processing as well as the float8-vs-numeric-preference issue before we'll > have spec-compliant processing of expressions like "numericvar * 1.234". > (And yes, there's been lots and lots of discussion about how to do that, > too...) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> On 17 Jun 2001 21:02:03 -0500, Ross J. Reedstrom wrote: > > On Sat, Jun 16, 2001 at 01:07:29AM -0400, Craig Longman wrote: > > > > <problem wiuth no automatic float8<->numeric casts> > > > > Tom Lane has already addressed the 'why is it this way' question. I'll > > address your proposed work arounds. > > he did. i tried looking in pg-hackers for most of 2000, but was unable > to find the thread(s). i'll keep looking though. Go to the Web TODO list and look at 'typeconv'. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026