Re: bigint datatype accepting floating numbers

Chris <pggeneral(at)designmagick(dot)com> writes:
> Just wondering if anyone can explain why this is happening. I thought
> int2, int4, int8 all accepted integers only (according to the way the
> docs read at least).

7.2 and before allow all these cases:

regression=# create table testint(smallnum int2, num int4, bignum int8);
CREATE
regression=# insert into testint (smallnum) values (123.7);
INSERT 139713 1
regression=# insert into testint (num) values (123.7);
INSERT 139714 1
regression=# insert into testint (bignum) values (123.7);
INSERT 139715 1
regression=# SELECT * from testint;
smallnum | num | bignum
----------+-----+--------
124 | |
| 124 |
| | 124
(3 rows)

7.2's behavior appears to be correct per spec. I read in SQL92 section
9.2 "Store assignment":

k) If the data type of T is numeric and there is an approxi-
mation obtained by rounding or truncation of the numerical
value of V for the data type of T, then the value of T is set
to such an approximation.

If there is no such approximation, then an exception condi-
tion is raised: data exception-numeric value out of range.

If the data type of T is exact numeric, then it is implementation-
defined whether the approximation is obtained by rounding or
by truncation.

There is a pghackers argument going on right now (in a rather leisurely
way; it's been an open topic for months) about what exactly we ought to
do here. I think we are going to have to distinguish store assignment
behavior from implicit casts inside expressions ... which is pretty
ugly, but there are too many problems we can't fix if all possible
casts are equally valid inside expressions. For store assignment,
both the source and the destination datatype are known with certainty
so there's no ambiguity about what to do.

regards, tom lane