Int8: problems

Hello Thomas,

I installed the whole PostgreSQL-bundle, including int8, out of the box
and now I'm trying to use the int8-datatype. It looks as if I can get a
value in the table but not in a 'normal' way out of it. I tried your
testscript itest.sql and below you see it's output.

Another (?) problem: when I put a unquoted value into an int8 (and also a
float8), I get the following message:
pgsql=> insert into qtest values(19990511161616);
NOTICE: Integer input '19990511161616' is out of range; promoted to float
INSERT 148320 1
pgsql=> select float8(q1) from qtest;
float8
--------------------
123
123
4.56789012345679e+15
4.56789012345679e+15
4.56789012345679e+15
19990511161616
(6 rows)

Maybe you know a solution to this problems? At the moment I use the
float8-datatype for large numbers, but, well: when there is an int8 why
not use it...

Thank you,

Jelle.

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

create table qtest(q1 int8, q2 int8);
CREATE

insert into qtest values('123','456');
INSERT 148298 1
insert into qtest values('123','4567890123456789');
INSERT 148299 1
insert into qtest values('4567890123456789','123');
INSERT 148300 1
insert into qtest values('4567890123456789','4567890123456789');
INSERT 148301 1
insert into qtest values('4567890123456789','-4567890123456789');
INSERT 148302 1

select * from qtest;
q1| q2
-------------------+-------------------
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
(5 rows)

select q1, -q1 as minus from qtest;
q1| minus
-------------------+-------------------
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
(5 rows)

select q1, q2, q1 + q2 as plus from qtest;
q1| q2| plus
-------------------+-------------------+-------------------
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
(5 rows)

select q1, q2, q1 - q2 as minus from qtest;
q1| q2| minus
-------------------+-------------------+-------------------
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
(5 rows)

select q1, q2, q1 * q2 as multiply from qtest
where q1 < 1000 or (q2 > 0 and q2 < 1000);
q1| q2| multiply
-------------------+-------------------+-------------------
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
(3 rows)

--select q1, q2, q1 * q2 as multiply qtest
-- where q1 < '1000'::int8 or (q2 > '0'::int8 and q2 < '1000'::int8);
select q1, q2, q1 / q2 as divide from qtest;
q1| q2| divide
-------------------+-------------------+-------------------
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644|9223091508918288644
(5 rows)

select q1, float8(q1) from qtest;
q1|float8
-------------------+--------------------
9223091508918288644|123
9223091508918288644|123
9223091508918288644|4.56789012345679e+15
9223091508918288644|4.56789012345679e+15
9223091508918288644|4.56789012345679e+15
(5 rows)

select q2, float8(q2) from qtest;
q2|float8
-------------------+---------------------
9223091508918288644|456
9223091508918288644|4.56789012345679e+15
9223091508918288644|123
9223091508918288644|4.56789012345679e+15
9223091508918288644|-4.56789012345679e+15
(5 rows)

select q1, int8(float8(q1)) from qtest;
q1| int8
-------------------+-------------------
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
9223091508918288644|9223091508918288644
(5 rows)

--------------------------------------------------------------
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(at)neroc(dot)nl
--------------------------------------------------------------