Handling Decimal Numbers in Java and PostgreSQL

Background

When working with decimal numbers such as percentages, monetary values, or other precise quantities, choosing the correct data types in both Java and PostgreSQL is critical to avoid precision loss and rounding errors.

---

Java Data Types: Float vs BigDecimal

• Float and Double

  • Use binary floating-point representation.

  • Can introduce subtle rounding errors (e.g., 0.1 stored as 0.10000000000000001).

  • Suitable for approximate scientific calculations or measurements where minor inaccuracies are acceptable.

• BigDecimal

  • Uses exact decimal representation.

  • Ideal for financial calculations, percentages, or anywhere exact decimal precision is required.

  • Stores scale (number of digits after the decimal point) explicitly.

---

PostgreSQL Data Types: FLOAT vs NUMERIC

• FLOAT (or DOUBLE PRECISION)

  • Approximate, binary floating-point storage.

  • Can cause precision errors similar to Java Float/Double.

• NUMERIC(precision, scale)

  • Exact decimal storage.

  • precision = total number of digits (left + right of decimal).

  • scale = number of digits after the decimal point.

  • Recommended for percentages, money, and exact decimals.

---

Mapping Java to PostgreSQL

Java Type	PostgreSQL Type	Notes
Float	REAL (approximate)	May lose precision
Double	DOUBLE PRECISION	May lose precision
BigDecimal	NUMERIC(p, s)	Exact decimal, preferred for precise data

---

Recommended Entity Mapping Example

Java: 

@Column(name = "over_delivery_percent", precision = 5, scale = 2)
private BigDecimal overDeliveryPercent;

This maps to PostgreSQL:

over_delivery_percent NUMERIC(5, 2)

• Allows values like 99.99, 0.01.

• Enforces precision and scale constraints at the database level.

---

Liquibase Example for Adding Column

<addColumn tableName="your_table_name">
    <column name="over_delivery_percent" type="NUMERIC(5,2)">
        <constraints nullable="true"/>
    </column>
</addColumn>

---

Conversion Notes

• To convert from String to BigDecimal in Java:

java

BigDecimal bd = new BigDecimal("123.45");

• To convert from char (digit) to BigDecimal:

java
BigDecimal bd = new BigDecimal(String.valueOf('7'));

---

Summary

• Prefer BigDecimal + NUMERIC for exact decimals.

• Avoid Float or Double where precision matters.

• Always specify precision and scale in your database schema to enforce limits.