Re: How to avoid trailing zero (after decimal point) for numeric type column

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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wed, Feb 28, 2018 at 5:34 AM, pkashimalla <praveenkumar52028(at)gmail(dot)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.
>
>