Alban Hertroys <haramrae@gmail.com> writes:
> On 11 October 2011 15:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> That's because a cast from float to int rounds, it doesn't truncate.
> I figured it would be something like that. Is that how it's defined in
> the SQL standard?
SQL99 says
Whenever an exact or approximate numeric value is assigned to
an exact numeric value site, an approximation of its value that
preserves leading significant digits after rounding or truncating
is represented in the declared type of the target. The value is
converted to have the precision and scale of the target. The choice
of whether to truncate or round is implementation-defined.
An approximation obtained by truncation of a numeric value N for an
<exact numeric type> T is a value V in T such that N is not closer
to zero than is V and there is no value in T between V and N.
An approximation obtained by rounding of a numeric value N for an
<exact numeric type> T is a value V in T such that the absolute
value of the difference between N and the numeric value of V is
not greater than half the absolute value of the difference between
two successive numeric values in T. If there is more than one such
value V, then it is implementation-defined which one is taken.
or in short, "you can do it in any reasonable fashion". It looks like
our code for this has used rint() since the day it was put in,
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=64d9b508939fb15d72fdfa825ee8938506764d66
regards, tom lane