FW: [JDBC] BIGINT vs Java's long - Mailing list pgsql-hackers

This came into the jdbc list

Apparently bigint is not really 8 bytes???

I test this out with psql

test=# create table testbigint (id serial, fp0 int8);
NOTICE:  CREATE TABLE will create implicit sequence 'testbigint_id_seq'
for SERIAL column 'testbigint.id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index
'testbigint_id_key' for table 'testbigint'
CREATE
test=# insert into testbigint (fp0) values (1);
INSERT 333698 1
test=# update testbigint set fp0 = -9223372036854775808 where id = 1;
ERROR:  int8 value out of range: "-9223372036854775808"


Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dav Coleman
Sent: August 7, 2001 11:37 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] BIGINT vs Java's long


According to the Java Language Specification,
http://java.sun.com/docs/books/jls/second_edition/html/typesValues.doc.h
tml#9151

"For long, from -9223372036854775808 to 9223372036854775807, inclusive"


Indeed, I have java code which generate random long's and println's
them, and I end up with values equal to -9223372036854775808.

I had those println's redirected to a .sql file which I ran against psql
to update some bigint columns, but I got    ERROR:  int8 value out of range: "-9223372036854775808"

Apparently bigint's don't like that value?

Well confused, since 8 bytes should be 8 freaking bytes, I turned to
JDBC.

That's when things got weird, first I tried declaring a long variable
with that value, and got a compilere error (Integer to large) or
something like that.

So I declared "long myBigint = Long.MIN_VALUE" and that compiled, but
when I tried using that value in a Statement.execute() I got the exact
same error.

Anyone know what's going on? Here's the test code, using
jdbc7.0-1.2.jar:


import java.sql.*;

public class testPGSQLbigint {
   public static void main( String[] args ) {      try {           Class.forName("org.postgresql.Driver");      } catch
(java.lang.ClassNotFoundExceptione) {           System.out.println( e );      }
 
      Connection db=null;      String url = "jdbc:postgresql:abinitio2";      try {           db =
DriverManager.getConnection(url,"dav","");     } catch ( SQLException e ) {           System.err.println( e );      }
 
      // the following gives a compiler error      //long bigint = -9223372036854775808;      long bigint =
Long.MIN_VALUE;     String sql_ = "update chembase set fp0 = "+bigint+" where id =
 
27948;";      System.out.println(sql_);
      try {           Statement st = db.createStatement();           st.execute( sql_ );           st.close();      }
catch( SQLException e ) {           System.err.println( e );      }   }
 


}



output:
$ java -classpath /opt/java/jars/jdbc7.0-1.2.jar:. "testPGSQLbigint"

update chembase set fp0 = -9223372036854775808 where id = 27948;
java.sql.SQLException: ERROR:  int8 value out of range:
"-9223372036854775808"



note this runs the same in linux and win2k (using Sun's SDK)



-- 
Dav Coleman
http://www.danger-island.com/dav/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html