Обсуждение: Is this a bug? Insert float into int column inserts rounded value instead of error.
Is this a bug? Insert float into int column inserts rounded value instead of error.
От
Matthew Schumacher
Дата:
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
Re: Is this a bug? Insert float into int column inserts rounded value instead of error.
От
Andrew Sullivan
Дата:
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