Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5I4toa39135 for ; Mon, 18 Jun 2001 00:55:50 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f5I4tk327790; Mon, 18 Jun 2001 00:55:46 -0400 (EDT) To: Craig Longman cc: PostgreSQL SQL List Subject: Re: casts and conversions In-reply-to: <992836022.2127.10.camel@jigra.begeek.com> References: <992668049.2128.5.camel@jigra.begeek.com> <20010617210203.A10168@rice.edu> <992836022.2127.10.camel@jigra.begeek.com> Comments: In-reply-to Craig Longman message dated "17 Jun 2001 23:47:02 -0400" Date: Mon, 18 Jun 2001 00:55:45 -0400 Message-ID: <27787.992840145@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/250 X-Sequence-Number: 3508 Craig Longman 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