2

I'm using BigDecimal type in Java project. I'm trying to save it using function with numeric field. Java and postgres have different value. E.g. Value of 56.97 from Java saves as 56.9699999999999989 in postgres. How can I prevent it?

Bob
  • 309
  • 2
  • 5
  • 17
  • 1
    What exactly is the data type of the column in the database? –  Feb 24 '20 at 12:20
  • ..and how are you saving the value on Java side? – Kayaman Feb 24 '20 at 12:22
  • @a_horse_with_no_name At the end there is a varchar column – Bob Feb 24 '20 at 12:23
  • @Kayaman I get it from json and make a query to save using postgres function. – Bob Feb 24 '20 at 12:23
  • 1
    Check this question: [Which PostgreSQL column type should be used to store a Java BigDecimal?](https://stackoverflow.com/questions/4643592/which-postgresql-column-type-should-be-used-to-store-a-java-bigdecimal) – Alex Sveshnikov Feb 24 '20 at 12:24
  • ***Never***, ever store numbers in a `varchar` column. –  Feb 24 '20 at 12:24
  • 2
    How do you convert that `varchar` value to a number in your SELECT statement? –  Feb 24 '20 at 12:24
  • 2
    @Bob instead of describing what you're doing, how about editing the question and showing the relevant parts of code. You're already doing one thing wrong by storing numeric data as text, but you might be doing other things wrong too. – Kayaman Feb 24 '20 at 12:24
  • @a_horse_with_no_name inValue = inValueFloat::varchar(100); Is it a main issue? – Bob Feb 24 '20 at 13:06

1 Answers1

5

If you go to postgresql docs: https://www.postgresql.org/docs/current/datatype-numeric.html

I guess you are using real or double precision which are described as "variable-precision, inexact".

If you want it to be exact, let's say we are talking about money or similar, then you should use numeric or decimal types:

NUMERIC(precision, scale)

The precision is the total number of digits, while the scale is the number of digits in the fraction part.

For example, the number 1234.567 has a precision of seven and a scale of three.

Buddy Christ
  • 1,364
  • 8
  • 22