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

2018-02-28 15:09 GMT+01:00 Praveen Kumar <praveenkumar52028(at)gmail(dot)com>:

> 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
>
>
then you have to do same cleaning on INSERT - or you different data type -
maybe "double precision" is better for you.

There is not strong equality between oracle's number and postgres's numeric.

Regards

Pavel

Please, don't do top post
https://en.wikipedia.org/wiki/Posting_style#Top-posting

>
>
> 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
>>>
>>>
>>
>