Re: [GENERAL] int8-datatype: possible bug - Mailing list pgsql-general

Hello José Soares,

thank you. I tried your testcase and all input was changed in the magical
value. The 'NOTICE' disappeared when I quoted the value, but the result
was all the same.

edahrecepten=> create table test(i8 int8);
CREATE
edahrecepten=> insert into test values(19980528112607 );
NOTICE:  Integer input '19980528112607' is out of range; promoted to float
INSERT 147055 1
edahrecepten=> select * from test;
                 i8
-------------------
9223091440198811892
(1 row)

edahrecepten=> insert into test values('19980528112607' );
INSERT 147056 1
edahrecepten=> select * from test;
                 i8
-------------------
9223091440198811892
9223091440198811892
(2 rows)

edahrecepten=> insert into test values('19980528112607'::int8);
INSERT 147057 1
edahrecepten=> select * from test;
                 i8
-------------------
9223091440198811892
9223091440198811892
9223091440198811892
(3 rows)

edahrecepten=> select version();
version
----------------------------------------------------
PostgreSQL 6.4.2 on mips-sgi-irix6.2, compiled by cc
(1 row)

So for the time being I use float8...

Jelle.

>Jelle Ruttenberg ha scritto:
>
>> Hello all,
>>
>> when I try to put a numeric 'timestamp' like 19980528112607 into an
>> int8-datatype I get the notice 'NOTICE:  Integer input '19980528112607'
>> is out of range; promoted to float' and the value is put in the database
>> as 9223091508918288644.
>> It doesn't matter if I change the value: it will allways be the magical
>> value 9223091508918288644.
>>
>> When I use the float8-datatype it all goes well.
>>
>> Is this a bug in PostgreSQL or has it something to do with
>> platform-specific limitations?
>>
>> Thanks,
>>
>> Jelle.
>>
>> PostgreSQL: 6.4.2
>> Platform: SGI IRIX 6.2. IP22
>>
>> --------------------------------------------------------------
>>                    NEROC Publishing Solutions
>>
>>                       Jelle Ruttenberg
>>
>> De Run 1131, 5503 LB Veldhoven    Phone  : +31-(0)40-2586641
>> P.O.Box 133, 5500 AC Veldhoven    Fax    : +31-(0)40-2541893
>> The Netherlands                   E-mail : ruttenberg@neroc.nl
>> --------------------------------------------------------------
>
>Let me try on v6.5beta1...
>
>prova=> create table test(i8 int8);
>CREATE
>prova=> insert into test values(19980528112607 );
>NOTICE:  Integer input '19980528112607' is out of range; promoted to float
>INSERT 145481 1
>prova=> insert into test values('19980528112607' );
>INSERT 145482 1
>prova=> insert into test values('19980528112607'::int8);
>INSERT 145483 1
>prova=> select * from test;
>            i8
>--------------
>19980528112607
>19980528112607
>19980528112607
>(3 rows)
>
>
>Seems that works fine. And now trying bounds...
>
>prova=> insert into test values(-9223372036854775808);
>NOTICE:  Integer input '9223372036854775808' is out of range; promoted to
>float
>ERROR:  Floating point conversion to int64 is out of range
>prova=> insert into test values('-9223372036854775808');
>INSERT 145486 1
>prova=> insert into test values(9223372036854775807);
>NOTICE:  Integer input '9223372036854775807' is out of range; promoted to
>float
>ERROR:  Floating point conversion to int64 is out of range
>prova=> insert into test values('9223372036854775807');
>INSERT 145487 1
>prova=> select * from test;
>                  i8
>--------------------
>      19980528112607
>      19980528112607
>      19980528112607
>-9223372036854775808
> 9223372036854775807
>(5 rows)
>
>
>It works if the data is enclosed with ''. :)
>
>prova=> select version();
>version
>-------------------------------------------------------------------
>PostgreSQL 6.5.0 on i586-pc-linux-gnulibc1, compiled by gcc 2.7.2.1
>(1 row)
>
>Jose'
>
>


--------------------------------------------------------------
                   NEROC Publishing Solutions

                      Jelle Ruttenberg

De Run 1131, 5503 LB Veldhoven    Phone  : +31-(0)40-2586641
P.O.Box 133, 5500 AC Veldhoven    Fax    : +31-(0)40-2541893
The Netherlands                   E-mail : ruttenberg@neroc.nl
--------------------------------------------------------------