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

Hi Pavel,

Selection is not the problem,
Insertion is the problem when we insert data with below query

*set lc_numeric to 'C';*
*INSERT INTO BLOB_TEST_TABLE(ID)VALUES (2500.0);*

And,
If I do the select after the above insert,

select * from public.blob_test_table where id = 2500;

id
numeric
-------------
*2500.0 *
------------------------------------------------------------------- THIS IS
NOT EXPECTED

But, I want it to be shown as below where 2500 without precision 0

It should show

id
numeric
-------------
*2500*
--------------------------------------------------------------------
EXPECTED OUTPUT

Thanks,
Praveen

On Wed, Feb 28, 2018 at 7:06 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> 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.h
>> tml
>>
>>
>