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

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.