Thread: Is this a bug? Insert float into int column inserts rounded value instead of error.

List,

One of the reasons why I use postgres is because you can insert data and
it will work or give you an error instead of converting, truncating,
etc... well I found a place where postgres makes an erroneous
assumption and I'm not sure this is by design.

When inserting a float such as 4.12322345 into a int column postgres
inserts 4 instead of returning an error telling you that your value
won't fit.  I would much rather have the error and check for it since I
can be sure I'll get 4.12322345 back out if I didn't get an error on insert.

Is this by design?  If so I think it should be changed so that postgres
will always return your data exactly as you entered it if there isn't an
error on insert.

template1=# create table test (number int);
CREATE TABLE
template1=# insert into test (number) values (4.123123123);
INSERT 0 1
template1=# select * from test;
 number
--------
      4
(1 row)


Thanks,
schu

Matthew Schumacher <matt.s@aptalaska.net> writes:
> template1=# create table test (number int);
> CREATE TABLE
> template1=# insert into test (number) values (4.123123123);
> INSERT 0 1

Perhaps you'd be happier doing it like this:

regression=# insert into test (number) values ('4.123123123');
ERROR:  invalid input syntax for integer: "4.123123123"

Or if you use an integer-typed parameter, or COPY, the same thing will
happen.  The point here being that the integer input function is picky
in the way you want, but that has nothing to do with whether an
acknowleged non-integral value can be converted to int.  The original
case is allowed because float to int is an "assignment" cast.  You could
change it to an explicit cast if you like, but I think you'd soon find
that unpleasant; and it would be contrary to the SQL spec.  SQL92
section 4.6 saith:

         Values of the data types NUMERIC, DECIMAL, INTEGER, SMALLINT,
         FLOAT, REAL, and DOUBLE PRECISION are numbers and are all mutually
         comparable and mutually assignable. If an assignment would result
         in a loss of the most significant digits, an exception condition
         is raised. If least significant digits are lost, implementation-
         defined rounding or truncating occurs with no exception condition
         being raised. The rules for arithmetic are generally governed by
         Subclause 6.12, "<numeric value expression>".

            regards, tom lane

On Mon, Aug 27, 2007 at 12:48:34PM -0800, Matthew Schumacher wrote:
> When inserting a float such as 4.12322345 into a int column postgres
> inserts 4 instead of returning an error telling you that your value
> won't fit.  I would much rather have the error and check for it since I
> can be sure I'll get 4.12322345 back out if I didn't get an error on insert.

If you quote it, it works.  That is:

testing=# SELECT 4.123123123::int;
 int4
------
    4
(1 row)

testing=# SELECT '4.123123123'::int;
ERROR:  invalid input syntax for integer: "4.123123123"

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to.  That actually seems sort of quaint now.
        --J.D. Baldwin