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

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.