Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5IEM5a94183 for ; Mon, 18 Jun 2001 10:22:05 -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 f5IELq301699; Mon, 18 Jun 2001 10:21:52 -0400 (EDT) To: "Josh Berkus" cc: Craig Longman , PostgreSQL SQL List Subject: Re: casts and conversions In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Sun, 17 Jun 2001 22:49:26 -0700" Date: Mon, 18 Jun 2001 10:21:52 -0400 Message-ID: <1696.992874112@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/252 X-Sequence-Number: 3510 "Josh Berkus" 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