Hi Pavel,Selection is not the problem,Insertion is the problem when we insert data with below queryset 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;idnumeric-------------2500.0 ------------------------------------------------------------------- THIS IS NOT EXPECTEDBut, I want it to be shown as below where 2500 without precision 0It should showidnumeric-------------2500-------------------------------------------------------------------- EXPECTED OUTPUT
Thanks,PraveenOn Wed, Feb 28, 2018 at 7:06 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:Hi2018-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 PostgreSQLyou can try to use a "to_char" function from orafce extension https://github.com/orafce/orafceides_jmmaj_prac=# set lc_numeric to 'C';SETTime: 0,219 msides_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)RegardsPavel Thanks, Praveen -- Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
Hi2018-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 PostgreSQLyou can try to use a "to_char" function from orafce extension https://github.com/orafce/orafceides_jmmaj_prac=# set lc_numeric to 'C';SETTime: 0,219 msides_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)RegardsPavel Thanks, Praveen -- Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
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
pgsql-bugs by date:
Соглашаюсь с условиями обработки персональных данных