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

Hi Pavel,

Correct, I have decided to change my application's code,

Changing PostgreSQL's source code is not the correct solution, as there
will be a complication when there is a need for PostgreSQL's updated
version.

Anyway, I like the behavior of PostgreSQL's numeric type , It just inserts
what you give instead of truncation as in oracle's Number type.

Thank you so much for patience and useful answers.

We can close this chain.

Thanks,
Praveen.K

On Thu, Mar 1, 2018 at 12:29 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> 2018-03-01 7:45 GMT+01:00 Praveen Kumar <praveenkumar52028(at)gmail(dot)com>:
>
>> 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?
>>
>
> you can hack Postgres and change, behave of numeric. But then there are
> issues with necessity of build own packages, ... so preferred way is use
> own numeric type like numeric2.
>
> any data type in PostgreSQL is defined by some sets of functions - for
> you, you need to modify numeric_in function - just truncate zero from right
> and call numeric_in. You can look postgresql source code
> postgresql/contrib/citext - citext is based on text type
>
> It is not lot of work. But it is not easy work, for people who don't know
> C language and PostgreSQL ecosystem.
>
>
>>
>> or clean inserted values on app side. - Does this means, Change my
>> application code?
>>
>
> yes. It is most easy and probably correct solution for you. Postgres is
> not Oracle, and Oracle is not Postgres, and some differences are best
> solved on application level.
>
> Regards
>
> Pavel
>
>
>
>
>>
>> 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.000000000000
>>> 0000000000000000000000000000000000000000000000000'::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
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>