Thread: How to avoid trailing zero (after decimal point) for numeric typecolumn
Hello Team, We have recently migrated our database from Oracle And there are few of my tables with numeric column type. In below example 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(); 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 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 Because of this, the migrated data (from Oracle) is without PRECISION ZERO and the new data which is being inserted is with PRECISION ZERO. / select id from blob_test_table; id numeric ------------- 10.0 10 11 11.0 / Is there a possible setting in PostgreSQL server to achieve this? FYI - Oracle's NUMBER column type is handling it as I expected. I migrate Oracle's NUMBER column as just NUMERIC column in PostgreSQL Thanks, Praveen -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: How to avoid trailing zero (after decimal point) for numeric type column
From
Charles Clavadetscher
Date:
Hello > On 28.02.2018, at 13:33, pkashimalla <praveenkumar52028@gmail.com> wrote: > > Hello Team, > > We have recently migrated our database from Oracle > And there are few of my tables with numeric column type. > > In below example > 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(); > > 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 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 > > Because of this, the migrated data (from Oracle) is without PRECISION ZERO > and the new data which is being inserted is with PRECISION ZERO. > > / > select id from blob_test_table; > > id > numeric > ------------- > 10.0 > 10 > 11 > 11.0 > / > > Is there a possible setting in PostgreSQL server to achieve this? > I think that you should set the colomn type to real or double precision: select 10.0::double precision; 10 select 10.5801::double precision; 10.5801 Regards Charles > FYI - > > Oracle's NUMBER column type is handling it as I expected. > I migrate Oracle's NUMBER column as just NUMERIC column in PostgreSQL > > > > Thanks, > Praveen > > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html >
Re: How to avoid trailing zero (after decimal point) for numeric type column
From
Francisco Olarte
Date:
On Wed, Feb 28, 2018 at 1:33 PM, pkashimalla <praveenkumar52028@gmail.com> 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.