Thread: bigint datatype accepting floating numbers

Hi all,

Sorry if this comes through multiple times.

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).


test=# SELECT version();
                                  version
------------------------------------------------------------------
    PostgreSQL 7.3b1 on i586-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)


test=# create table testint(smallnum int2, num int4, bignum int8);
CREATE TABLE

test=# insert into testint (smallnum) values (123.7);
ERROR:  column "smallnum" is of type 'smallint' but expression is of
type 'double precision'
             You will need to rewrite or cast the expression

test=# insert into testint (num) values (123.7);
ERROR:  column "num" is of type 'integer' but expression is of type
'double precision'
             You will need to rewrite or cast the expression

test=# insert into testint (bignum) values (123.7);
INSERT 17320 1

It seems to round the number too:

test=# SELECT * from testint;
      smallnum | num | bignum
----------+-----+--------
               |     |    124
(1 row)

Chris <pggeneral@designmagick.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