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

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

> maybe "double precision" is better for you. - Yes Pavel,I thought of
> using doible precion,but as per the documentation
>
> double precision 8 bytes variable-precision, inexact 15 decimal digits
> precisionIt can accept only 15 digits, but my tables may have more than
> that.
>
> That is why looking for an alternative.
>

You can implement own numeric type, that will try remove trailing zeros by
default. It is few days work - or clean inserted values on app side.

trailing zeros has not impact on value size - so there should not be any
issue, if you store it

ides_jmmaj_prac=# select pg_column_size('1.0'::numeric);
┌────────────────┐
│ pg_column_size │
╞════════════════╡
│ 8 │
└────────────────┘
(1 row)

Time: 0,481 ms
ides_jmmaj_prac=# select
pg_column_size('1.0000000000000000000000000000000000000000000000000000000000000'::numeric);
┌────────────────┐
│ pg_column_size │
╞════════════════╡
│ 8 │
└────────────────┘
(1 row)

Regards

Pavel

>
>
> Thanks,
> Praveen
>
>
>
> On Wed, Feb 28, 2018 at 7:42 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>>
>> 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.html
>>>>>
>>>>>
>>>>
>>>
>>
>