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

On Wed, Feb 28, 2018 at 1:33 PM, pkashimalla
<praveenkumar52028(at)gmail(dot)com> wrote:
...
> 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();

That's incomplete. But why are you using Double to bind a NUMERIC?
IIRC BigDecimal seems to me the natural java conunterpart to it.

>
> 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 the driver is supposed to know this how?

> 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

Doubles do not have precision ( well, they have, double precision ).
Also note doubles are binary, and that 10.5801 is not bounded in
binary. This is perl but you can see the thing:

$ perl -w -e 'printf q(%1$f %1$.20f %1$a %1$.20a)."\n", 10.5801'
10.580100 10.58009999999999983800 0x1.52902de00d1b7p+3
0x1.52902de00d1b70000000p+3

Note how the decimal varies with the requested precision, as 10.5801
cannot be represented exactly in binary, and how the hex
representation does not vary ( because hexadecimal has an exact binary
representation ).

> Because of this, the migrated data (from Oracle) is without PRECISION ZERO
> and the new data which is being inserted is with PRECISION ZERO.

I think you are hitting an implementation-defined behaviour of the
driver. To convert a double to a numeric you must select a precision,
and it seems oracle and postgres do it differently. I would try
BigDecimal which has less uncertainity.

> Oracle's NUMBER column type is handling it as I expected.

Oracle behaves as you are used to, and so is what you expect.

> I migrate Oracle's NUMBER column as just NUMERIC column in PostgreSQL

Probably using a BigDecimal would do the trick, as you can convert sql
numeric => java bigdecimal => sql numeric without losing info, but not
with Doubles. Bear in mind numeric is decimal, double is binary, and
one thing such simpel looking as 0.1 does not have an exact binary
representation:

$ perl -w -e 'printf q(%1$f %1$.20f %1$a %1$.20a)."\n", 0.1'
0.100000 0.10000000000000000555 0x1.999999999999ap-4 0x1.999999999999a0000000p-4

See the trailing 555 in the second? or the continous 1.(9) fraction
rounded to a in excess on the least significant place in hex?

In numeric you can hace 1.0, 1.00000, 1.00000000000000000000000000001
and 0.999999999999999999999999999999, but in doubles they all map to
1.0

Francisco Olarte.