Обсуждение: How to avoid trailing zero (after decimal point) for numeric typecolumn

Поиск
Список
Период
Сортировка

How to avoid trailing zero (after decimal point) for numeric typecolumn

От
pkashimalla
Дата:
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 



Thanks, 
Praveen 



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html

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

От
Pavel Stehule
Дата:
Hi

2018-02-28 13:34 GMT+01:00 pkashimalla <praveenkumar52028@gmail.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


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

От
Praveen Kumar
Дата:
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.stehule@gmail.com> wrote:
Hi

2018-02-28 13:34 GMT+01:00 pkashimalla <praveenkumar52028@gmail.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



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

От
Pavel Stehule
Дата:


2018-02-28 15:09 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.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

 


Thanks,
Praveen


On Wed, Feb 28, 2018 at 7:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

2018-02-28 13:34 GMT+01:00 pkashimalla <praveenkumar52028@gmail.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




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

От
Praveen Kumar
Дата:
maybe "double precision" is better for you.  -   Yes Pavel,I thought of using doible precion,but as per the documentation

double precision8 bytesvariable-precision, inexact15 decimal digits precision
It can accept only 15 digits, but my tables may have more than that.

That is why looking for an alternative.


Thanks,
Praveen



On Wed, Feb 28, 2018 at 7:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:


2018-02-28 15:09 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.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

 


Thanks,
Praveen


On Wed, Feb 28, 2018 at 7:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

2018-02-28 13:34 GMT+01:00 pkashimalla <praveenkumar52028@gmail.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





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

От
Pavel Stehule
Дата:


2018-02-28 15:23 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:
maybe "double precision" is better for you.  -   Yes Pavel,I thought of using doible precion,but as per the documentation

double precision8 bytesvariable-precision, inexact15 decimal digits precision
It 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.stehule@gmail.com> wrote:


2018-02-28 15:09 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.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

 


Thanks,
Praveen


On Wed, Feb 28, 2018 at 7:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

2018-02-28 13:34 GMT+01:00 pkashimalla <praveenkumar52028@gmail.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






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

От
"David G. Johnston"
Дата:
On Wed, Feb 28, 2018 at 5:34 AM, pkashimalla <praveenkumar52028@gmail.com> wrote:
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

​I'm sorry but you told it to insert 10.0 and it did.  This is not a bug but the system doing exactly as you asked it to.  This conversation is appropriate for -general, not -bugs.

The only bug I found looking at this is that double precision table row you show down-thread says 15-digits precision while the paragraph covering this says: "The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits."  I'm not sure which one is correct - you may want to load some of your larger numbers and verify behavior for yourself.

https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-FLOAT
​
I'm curious if you can state a reason behind this desire other than familiarity from past Oracle experience.  From a GUI presentation perspective I can understand the desire here but odds are you already would be using something to to_char to get clean presentation.  The fact that PostgreSQL is storing exactly what you told it internally is hard to argue as being wrong.  You will likely need to fix your client code or input data if you truly need this fixed in the manner you say - or put a CASE expression into an INSERT/UPDATE TRIGGER.

David J.

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

От
Praveen Kumar
Дата:
Hey David, Answering below question - 

I'm curious if you can state a reason behind this desire other than familiarity from past Oracle experience   
-  It because our application is very old and huge also in most of the places we are executing a statement like below,

preparestatement.setDouble(id,5.0) ;

And Oracle stores it as 5(Number data type) and PostgreSQL stores it as 5.0 (which is valid as per definition of numeric data type),

And in application code, to retrieve this id,we have used

String id = resultSet.getString("id");

Later somewhere in code executing below logic,

Integer id = Integer.ValueOf( id ); - This results in NUMBER FORMAT exception if the selected id is like 5.0( because its double in reality ) and works fine if selected id is 5

So, I don't want to change my Insert or any other logic in my application (There are so many such instances), instead if I can find a right way to store 5.0 as 5 for the same numeric type, The problem solves.


Thanks,
Praveen

On Wed, Feb 28, 2018 at 8:06 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Feb 28, 2018 at 5:34 AM, pkashimalla <praveenkumar52028@gmail.com> wrote:
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

​I'm sorry but you told it to insert 10.0 and it did.  This is not a bug but the system doing exactly as you asked it to.  This conversation is appropriate for -general, not -bugs.

The only bug I found looking at this is that double precision table row you show down-thread says 15-digits precision while the paragraph covering this says: "The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits."  I'm not sure which one is correct - you may want to load some of your larger numbers and verify behavior for yourself.

https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-FLOAT
​
I'm curious if you can state a reason behind this desire other than familiarity from past Oracle experience.  From a GUI presentation perspective I can understand the desire here but odds are you already would be using something to to_char to get clean presentation.  The fact that PostgreSQL is storing exactly what you told it internally is hard to argue as being wrong.  You will likely need to fix your client code or input data if you truly need this fixed in the manner you say - or put a CASE expression into an INSERT/UPDATE TRIGGER.

David J.


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

От
Praveen Kumar
Дата:
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.stehule@gmail.com> wrote:


2018-02-28 15:23 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:
maybe "double precision" is better for you.  -   Yes Pavel,I thought of using doible precion,but as per the documentation

double precision8 bytesvariable-precision, inexact15 decimal digits precision
It 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.stehule@gmail.com> wrote:


2018-02-28 15:09 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.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

 


Thanks,
Praveen


On Wed, Feb 28, 2018 at 7:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

2018-02-28 13:34 GMT+01:00 pkashimalla <praveenkumar52028@gmail.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







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

От
Pavel Stehule
Дата:


2018-03-01 7:45 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.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.stehule@gmail.com> wrote:


2018-02-28 15:23 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:
maybe "double precision" is better for you.  -   Yes Pavel,I thought of using doible precion,but as per the documentation

double precision8 bytesvariable-precision, inexact15 decimal digits precision
It 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.stehule@gmail.com> wrote:


2018-02-28 15:09 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.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

 


Thanks,
Praveen


On Wed, Feb 28, 2018 at 7:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

2018-02-28 13:34 GMT+01:00 pkashimalla <praveenkumar52028@gmail.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








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

От
Praveen Kumar
Дата:
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.stehule@gmail.com> wrote:


2018-03-01 7:45 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.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.stehule@gmail.com> wrote:


2018-02-28 15:23 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:
maybe "double precision" is better for you.  -   Yes Pavel,I thought of using doible precion,but as per the documentation

double precision8 bytesvariable-precision, inexact15 decimal digits precision
It 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.stehule@gmail.com> wrote:


2018-02-28 15:09 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.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

 


Thanks,
Praveen


On Wed, Feb 28, 2018 at 7:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

2018-02-28 13:34 GMT+01:00 pkashimalla <praveenkumar52028@gmail.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