SQL.How do numeric types like INTEGER, BIGINT, DECIMAL, and FLOAT differ in terms of precision and use cases?

Short, interview-ready answer

INTEGER and BIGINT are exact whole numbers, DECIMAL is exact for fractional values, and FLOAT is approximate and should never be used for money.
The choice affects correctness, not just storage.

1️⃣ INTEGER / BIGINT — exact whole numbers

What they are

• Fixed-precision
• Store exact values
• No rounding

INTEGER   -- usually 32-bit
BIGINT    -- usually 64-bit

Typical ranges

• INTEGER: ~ ±2 billion
• BIGINT: ~ ±9 quintillion

Use cases

✅ IDs, counters, quantities
✅ Status codes
✅ Amounts in minor units (cents)

Example

balance_cents BIGINT

Interview insight

If money has no fractions → integers are perfect.

2️⃣ DECIMAL(p, s) / NUMERIC — exact decimals

What it is

• Arbitrary precision
• Exact representation of decimals
• Slower, but correct

DECIMAL(10, 2)

p = total digits

s = digits after decimal point

Use cases

✅ Money
✅ Financial calculations
✅ Rates, percentages, measurements where correctness matters

Example

price DECIMAL(10,2)

Why it matters

0.1 + 0.2 = 0.3  -- TRUE with DECIMAL

3️⃣ FLOAT / REAL / DOUBLE — approximate numbers 🚨

What they are

• Floating-point (IEEE-754)
• Approximate
• Fast, hardware-supported

REAL
DOUBLE PRECISION

The classic trap

0.1 + 0.2 = 0.30000000000000004

Use cases

✅ Scientific data
✅ Metrics
✅ Sensor data
✅ ML / statistics
❌ Money ❌

Interview rule

Never use FLOAT for financial data.

4️⃣ Precision comparison (must remember)

Type	Exact?	Fractional?	Typical use
INTEGER	✅	❌	IDs, counters
BIGINT	✅	❌	Large counters, money in cents
DECIMAL	✅	✅	Money, finance
FLOAT	❌	✅	Scientific, analytics

5️⃣ Performance vs correctness trade-off

• INTEGER / BIGINT → fastest, exact
• DECIMAL → slower, exact
• FLOAT → fastest for math, inexact

Senior mindset:

Correctness first, performance second.

---

6️⃣ Common real-world mistakes 🚩

❌ Using FLOAT for prices
❌ Mixing DECIMAL and FLOAT in calculations
❌ Using DECIMAL without defining scale
❌ Storing money as DOUBLE “because Java double”

---

7️⃣ PostgreSQL-specific note (bonus)

• NUMERIC == DECIMAL
• Unlimited precision (bounded by memory)
• Safe for finance, but avoid unnecessary precision

Interview-ready final answer (clean)

“INTEGER and BIGINT store exact whole numbers and are ideal for IDs and counters.
DECIMAL stores exact fractional values and is the correct choice for money and financial data.
FLOAT uses approximate representation and should be used only where small rounding errors are acceptable, such as scientific or statistical data.”