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

Hey Pavel,

I am okay with the size

You can implement own numeric type, that will try to remove trailing zeros
by default. - Can I override the existing numeric type, or do you want me
to create a custom numeric type?

or clean inserted values on app side. - Does this means, Change my
application code?

Thanks,
Praveen

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

>
>
> 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.000000000000000000000000000000
> 0000000000000000000000000000000'::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
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>