Обсуждение: Default numeric scale of zero in JDBC?
Hello, I have discovered that the jdbc driver apparently uses a default scale of zero decimal places for SQL numeric operators,including the sum() aggregate operator. For example, this query: select (1.5 + 2); This returns 4 from jdbc, but it returns 3.5 when run from within PGAdmin. The only way I can coerce the value to returndecimal places is to explicity cast the value to a numeric with scale: select cast(1.5 + 2 as numeric(10,2)) as value This returns 3.5. Also, queries like "select sum(value) from table" returns a rounded value, even though the column 'value' is numeric(16,2). Is there a magic vmparam or setting to default the precision at the jdbc driver level? I am trying to runa large existing system on PostgreSQL and it is not feasible to hunt down all numeric operations in thousands of classesto explicitly cast them. Have others have encountered this issue? Thanks, -Todd
Hello, I downloaded the jdbc source and noted that if I specified a default scale of 4 in AbstractJdbc2ResultSetMetadata.getScale()(if the column is numeric and the scale is -1), I received the desired results. I further updated the code to provide a private DEFAULT_NUMERIC_PRECISION that defaults to 4, which can be overriddenwith the System property org.postgresql.jdbc2.DEFAULT_NUMERIC_SCALE. I built this and tested it within SquirrelSQL and it works properly, and the system property is working to override this default. Would this be an updatethat the jdbc driver developers would be interested in? I suppose my other question is, what do other JDBC drivers default this to? Also, does this imply that others do not usenon-integer arithmetic with sql through JDBC? -Todd ----- Original Message ---- From: Todd Shoemaker <jtshoe11@yahoo.com> To: pgsql-jdbc@postgresql.org Sent: Thursday, January 18, 2007 8:05:42 AM Subject: [JDBC] Default numeric scale of zero in JDBC? Hello, I have discovered that the jdbc driver apparently uses a default scale of zero decimal places for SQL numeric operators,including the sum() aggregate operator. For example, this query: select (1.5 + 2); This returns 4 from jdbc, but it returns 3.5 when run from within PGAdmin. The only way I can coerce the value to returndecimal places is to explicity cast the value to a numeric with scale: select cast(1.5 + 2 as numeric(10,2)) as value This returns 3.5. Also, queries like "select sum(value) from table" returns a rounded value, even though the column 'value' is numeric(16,2). Is there a magic vmparam or setting to default the precision at the jdbc driver level? I am trying to runa large existing system on PostgreSQL and it is not feasible to hunt down all numeric operations in thousands of classesto explicitly cast them. Have others have encountered this issue? Thanks, -Todd ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
Todd Shoemaker wrote: > Hello, > > I downloaded the jdbc source and noted that if I specified a default scale of 4 in AbstractJdbc2ResultSetMetadata.getScale()(if the column is numeric and the scale is -1), I received the desired results. I further updated the code to provide a private DEFAULT_NUMERIC_PRECISION that defaults to 4, which can be overriddenwith the System property org.postgresql.jdbc2.DEFAULT_NUMERIC_SCALE. I built this and tested it within SquirrelSQL and it works properly, and the system property is working to override this default. Would this be an updatethat the jdbc driver developers would be interested in? > > I suppose my other question is, what do other JDBC drivers default this to? Also, does this imply that others do not usenon-integer arithmetic with sql through JDBC? Given that the reported metadata scale does not affect what the driver does at all, and all the computation is done on the server side, I'd say that any rounding that is happening is happening in whatever is using the driver's metadata ("Squirrel SQL?") -O