Postgres Pro
Downloads
Home   >   mailing lists

Re: How to avoid trailing zero (after decimal point) for numeric type column - Mailing list pgsql-bugs

From Praveen Kumar
Subject Re: How to avoid trailing zero (after decimal point) for numeric type column
Date
Msg-id CAG2WJO1qSdMkwxFjNNbcR-xvGYKyygAizuem02bj7THOFLQygg@mail.gmail.com
Whole thread Raw
In response to Re: How to avoid trailing zero (after decimal point) for numeric type column  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-bugs
Tree view
Hey David, Answering below question - 

I'm curious if you can state a reason behind this desire other than familiarity from past Oracle experience   
-  It because our application is very old and huge also in most of the places we are executing a statement like below,

preparestatement.setDouble(id,5.0) ;

And Oracle stores it as 5(Number data type) and PostgreSQL stores it as 5.0 (which is valid as per definition of numeric data type),

And in application code, to retrieve this id,we have used

String id = resultSet.getString("id");

Later somewhere in code executing below logic,

Integer id = Integer.ValueOf( id ); - This results in NUMBER FORMAT exception if the selected id is like 5.0( because its double in reality ) and works fine if selected id is 5

So, I don't want to change my Insert or any other logic in my application (There are so many such instances), instead if I can find a right way to store 5.0 as 5 for the same numeric type, The problem solves.


Thanks,
Praveen

On Wed, Feb 28, 2018 at 8:06 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Feb 28, 2018 at 5:34 AM, 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

​I'm sorry but you told it to insert 10.0 and it did.  This is not a bug but the system doing exactly as you asked it to.  This conversation is appropriate for -general, not -bugs.

The only bug I found looking at this is that double precision table row you show down-thread says 15-digits precision while the paragraph covering this says: "The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits."  I'm not sure which one is correct - you may want to load some of your larger numbers and verify behavior for yourself.

https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-FLOAT
​
I'm curious if you can state a reason behind this desire other than familiarity from past Oracle experience.  From a GUI presentation perspective I can understand the desire here but odds are you already would be using something to to_char to get clean presentation.  The fact that PostgreSQL is storing exactly what you told it internally is hard to argue as being wrong.  You will likely need to fix your client code or input data if you truly need this fixed in the manner you say - or put a CASE expression into an INSERT/UPDATE TRIGGER.

David J.


pgsql-bugs by date:

Previous
From: Michael Paquier
Date:
Subject: Re: Conflicting declarations for b64_encode etc. on Solaris 11.4 Beta
Next
From: Praveen Kumar
Date:
Subject: Re: How to avoid trailing zero (after decimal point) for numeric type column