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

Matthew Schumacher <matt(dot)s(at)aptalaska(dot)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