The following bug has been logged online: Bug reference: 1712 Logged by: Gilles Dubochet Email address: [hidden email] PostgreSQL version: 8.0.2 Operating system: Linux Description: JDBC column precision for bigint is 0 Details: Whith the JDBC driver at least up to version 8.1dev-400, the result of the getPrecision method of ResultSetMetaData on a bigint column is 0 instead of the expected 19. Code seems to be missing in org.postgresql.jdbc2.AbstractJdbc2ResultSetMetadata after line 359 (in source code 8.1dev-400). Java code to reproduce problem: import java.sql.*; public class Main { public static void main(String[] args) throws Exception { Class.forName("org.postgresql.Driver"); Connection con = DriverManager.getConnection("jdbc:postgresql://localhost/pguser", "pguser", ""); Statement stmt = con.createStatement(); stmt.execute("DROP TABLE mytable"); stmt.execute("CREATE TABLE mytable (bi BIGINT)"); System.out.println("We get "+ stmt.executeQuery("select * from mytable").getMetaData().getPrecision(1)+ " but should get 19"); } } ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [hidden email] so that your message can get through to the mailing list cleanly |
Gilles Dubochet wrote:
> The following bug has been logged online: > > Bug reference: 1712 > Logged by: Gilles Dubochet > Email address: [hidden email] > PostgreSQL version: 8.0.2 > Operating system: Linux > Description: JDBC column precision for bigint is 0 > Details: > > Whith the JDBC driver at least up to version 8.1dev-400, the result of the > getPrecision method of ResultSetMetaData on a bigint column is 0 instead of > the expected 19. This has been reported before but I haven't got to fixing it yet. This is partly because I haven't seen a good explanation of exactly what we should be returning here -- what spec says we should return 19? -O ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend |
>> Whith the JDBC driver at least up to version 8.1dev-400, the
>> result of the >> getPrecision method of ResultSetMetaData on a bigint column is 0 >> instead of >> the expected 19. >> > > This has been reported before but I haven't got to fixing it yet. This > is partly because I haven't seen a good explanation of exactly what we > should be returning here -- what spec says we should return 19? > Well, in PostgreSQL, BIGINT uses 8 bytes (that is what the documentation says, at least). Now, with 8 bytes, the range of numbers that can be represented is: For 63 bits + 1 sign bit: [ (2^63/2)-1, -2^63/2] = [9223372036854775807, -9223372036854775808] For 64 bits (unsigned): [2^64, 0] = [18446744073709551616, 0] If you count the number of digits in these numbers, you'll notice that for the signed number, 19 decimal digits at most are required to represent it (if the sign comes for free, which seems assumed for other data types such as INT or SMALLINT). For the unsigned number, 20 decimal digits are required. But as far as I understand the PostgreSQL reference, integers are always signed, except for serial data types, but where the range is that of a signed number anyway (since they need to be compatible with "normal" integer types to represent references). This is why I believe 19 is the value the getPrecision method should return. I don't think there is some kind of standard reference that defines it, but it seems pretty clear what it should be really. I hope this convinces you, if you still have any doubts, let me know: I'll do my best to appease them ;) Sincerely, Gilles Dubochet. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
In reply to this post by Gilles Dubochet
I have no official standing, but I've spent a lot of time reading the
JDBC spec and working with various implementations, and I concur. Precision should be the maximum number of significant digits the column is capable of returning. -Kevin >>> Gilles Dubochet <[hidden email]> 06/13/05 3:27 AM >>> >> Whith the JDBC driver at least up to version 8.1dev-400, the >> result of the >> getPrecision method of ResultSetMetaData on a bigint column is 0 >> instead of >> the expected 19. >> > > This has been reported before but I haven't got to fixing it yet. This > is partly because I haven't seen a good explanation of exactly what we > should be returning here -- what spec says we should return 19? > Well, in PostgreSQL, BIGINT uses 8 bytes (that is what the documentation says, at least). Now, with 8 bytes, the range of numbers that can be represented is: For 63 bits + 1 sign bit: [ (2^63/2)-1, -2^63/2] = [9223372036854775807, -9223372036854775808] For 64 bits (unsigned): [2^64, 0] = [18446744073709551616, 0] If you count the number of digits in these numbers, you'll notice that for the signed number, 19 decimal digits at most are required to represent it (if the sign comes for free, which seems assumed for other data types such as INT or SMALLINT). For the unsigned number, 20 decimal digits are required. But as far as I understand the PostgreSQL reference, integers are always signed, except for serial data types, but where the range is that of a signed number anyway (since they need to be compatible with "normal" integer types to represent references). This is why I believe 19 is the value the getPrecision method should return. I don't think there is some kind of standard reference that defines it, but it seems pretty clear what it should be really. ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match |
In reply to this post by Gilles Dubochet
Gilles Dubochet wrote:
>>> Whith the JDBC driver at least up to version 8.1dev-400, the result >>> of the >>> getPrecision method of ResultSetMetaData on a bigint column is 0 >>> instead of >>> the expected 19. >>> >> >> This has been reported before but I haven't got to fixing it yet. This >> is partly because I haven't seen a good explanation of exactly what we >> should be returning here -- what spec says we should return 19? > If you count the number of digits in these numbers, you'll notice that > for the signed number, 19 decimal digits at most are required to > represent it (if the sign comes for free, which seems assumed for other > data types such as INT or SMALLINT). [...] > This is why I believe 19 is the value the getPrecision method should > return. I don't think there is some kind of standard reference that > defines it, but it seems pretty clear what it should be really. The problem I have is how we decide that "precision" means "number of digits, ignoring sign". Actually, I just dug through the JDBC javadoc, and noticed that ResultSetMetaData.getPrecision() does talk about the number of decimal digits; but in all the other places it's used (such as DatabaseMetaData) it's not described. For numeric types using number of decimal digits seems reasonable since it's consistent with the NUMERIC type's precision/scale information. Previous reports of this also wanted to provide precision for dates and times according to the number of digits in them, which seems much more dubious. I guess it's another case of the JDBC specification being woefully underspecified :( -O ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
In reply to this post by Gilles Dubochet
I agree on all counts. The only way that I would extend the description
of "the number of decimal digits" is that I think it's fairly clear that for floating point numbers it should be "the number of significant digits" since the fractional part is often only an approximation of any decimal representation, and its representation could go on forever. java.util.Date and its java.sql subclasses have well defined formats. No elaboration with the precision is required, and I've never seen any hint that it is intended to apply. As someone tasked with keeping a software framework functional and efficient with multiple database products through standard JDBC, I often find myself dealing with different vendor interpretations of the softer parts of the JDBC spec. Each vendor is understandably reluctant to switch from a plausible interpretation they've already implemented to someone else's interpretation. -Kevin >>> Oliver Jowett <[hidden email]> 06/13/05 4:57 PM >>> Actually, I just dug through the JDBC javadoc, and noticed that ResultSetMetaData.getPrecision() does talk about the number of decimal digits; but in all the other places it's used (such as DatabaseMetaData) it's not described. For numeric types using number of decimal digits seems reasonable since it's consistent with the NUMERIC type's precision/scale information. Previous reports of this also wanted to provide precision for dates and times according to the number of digits in them, which seems much more dubious. I guess it's another case of the JDBC specification being woefully underspecified :( ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [hidden email] |
Free forum by Nabble | Edit this page |