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

Hi

2018-02-28 13:34 GMT+01:00 pkashimalla <praveenkumar52028(at)gmail(dot)com>:

> 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
>
>
you can try to use a "to_char" function from orafce extension
https://github.com/orafce/orafce

ides_jmmaj_prac=# set lc_numeric to 'C';
SET
Time: 0,219 ms
ides_jmmaj_prac=# select to_char(123.22000);
┌─────────┐
│ to_char │
╞═════════╡
│ 123.22 │
└─────────┘
(1 row)

Or PostgreSQL function

ides_jmmaj_prac=# select to_char(123.22000, 'FM99999.9999');
┌─────────┐
│ to_char │
╞═════════╡
│ 123.22 │
└─────────┘
(1 row)

Regards

Pavel

>
>
> Thanks,
> Praveen
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
>
>