If it’s money, use NUMERIC(p, s) / DECIMAL(p, s)—always.

Most engineers get burned by this: storing money with the wrong data type. I recently fixed a bug where a value like 23434554.34 couldn’t be saved because the column was defined as an integer. This post exists to correct that misconception—because in real systems, especially fintech, this mistake is costly.

In SQL, data types are contracts. Integers (SMALLINT, INTEGER, BIGINT) store only whole numbers. In PostgreSQL, any value with decimals must use either NUMERIC/DECIMAL (exact precision) or floating-point types (REAL, DOUBLE PRECISION). The problem? Floating-point types are approximate—they introduce rounding errors due to binary representation. That might be fine for analytics, but it’s unacceptable for financial data where every cent must reconcile.

So here’s the rule:

This guarantees exact storage and calculation. No rounding drift, no reconciliation gaps, no surprises in production.

ORMs won’t protect you unless you’re explicit:

// JPA / Hibernate

@Column(precision = 12, scale = 2)

private BigDecimal amount;

// Laravel Eloquent

$table->decimal('amount', 12, 2);

# Phoenix Ecto

field :amount, :decimal

// .NET EF Core

builder.Property(t => t.Amount)

.HasColumnType("decimal(12,2)");

// TypeORM

@Column("decimal", { precision: 12, scale: 2 })

amount: string;

// Prisma

amount Decimal @db.Decimal(12, 2)

// Sequelize

amount: { type: DataTypes.DECIMAL(12, 2) }

Common failure patterns:

Using FLOAT/DOUBLE for currency → silent rounding errors

Using INTEGER/BIGINT and later needing decimals → schema refactors

Letting ORMs infer types → inconsistent mappings

Ignoring precision/scale → broken calculations

Bottom line: your system’s financial correctness starts at the schema level. Be deliberate. Money is not “just a number.”

I am Usha, Senior Software Developer with specialty in Fintech and HealthCare systems. More about me here https://lnkd.in/e85eSXRx