On Wed, Feb 28, 2018 at 1:33 PM, pkashimalla <praveenkumar52028@xxxxxxxxx> wrote: ... > I did insertion from java program with below code snippet > > Double object = 10.0; > String inserRecordQuery_NEWWAY11 = "INSERT INTO BLOB_TEST_TABLE(id)VALUES > (?)"; > selectPrepareStmt.setObject(1, object,Types.NUMERIC); > int count = selectPrepareStmt.executeUpdate(); That's incomplete. But why are you using Double to bind a NUMERIC? IIRC BigDecimal seems to me the natural java conunterpart to it. > > it inserted like this. > / > select id from blob_test_table; > > id > numeric > ------------- > 10.0/ > > > In this case, when a decimal point is equal to 0 then, I don't want to see > the precision and the value in the column should just 10 And the driver is supposed to know this how? > And If I execute code, > > Double object = 10.5801 > String inserRecordQuery_NEWWAY11 = "INSERT INTO BLOB_TEST_TABLE(id)VALUES > (?)"; > selectPrepareStmt.setObject(1, object,Types.NUMERIC); > int count = selectPrepareStmt.executeUpdate(); > > Now ,the value in the column should be 10.5801 as the precision is greater > than ZERO Doubles do not have precision ( well, they have, double precision ). Also note doubles are binary, and that 10.5801 is not bounded in binary. This is perl but you can see the thing: $ perl -w -e 'printf q(%1$f %1$.20f %1$a %1$.20a)."\n", 10.5801' 10.580100 10.58009999999999983800 0x1.52902de00d1b7p+3 0x1.52902de00d1b70000000p+3 Note how the decimal varies with the requested precision, as 10.5801 cannot be represented exactly in binary, and how the hex representation does not vary ( because hexadecimal has an exact binary representation ). > Because of this, the migrated data (from Oracle) is without PRECISION ZERO > and the new data which is being inserted is with PRECISION ZERO. I think you are hitting an implementation-defined behaviour of the driver. To convert a double to a numeric you must select a precision, and it seems oracle and postgres do it differently. I would try BigDecimal which has less uncertainity. > Oracle's NUMBER column type is handling it as I expected. Oracle behaves as you are used to, and so is what you expect. > I migrate Oracle's NUMBER column as just NUMERIC column in PostgreSQL Probably using a BigDecimal would do the trick, as you can convert sql numeric => java bigdecimal => sql numeric without losing info, but not with Doubles. Bear in mind numeric is decimal, double is binary, and one thing such simpel looking as 0.1 does not have an exact binary representation: $ perl -w -e 'printf q(%1$f %1$.20f %1$a %1$.20a)."\n", 0.1' 0.100000 0.10000000000000000555 0x1.999999999999ap-4 0x1.999999999999a0000000p-4 See the trailing 555 in the second? or the continous 1.(9) fraction rounded to a in excess on the least significant place in hex? In numeric you can hace 1.0, 1.00000, 1.00000000000000000000000000001 and 0.999999999999999999999999999999, but in doubles they all map to 1.0 Francisco Olarte.
