Compare

DECIMAL vs NUMERIC vs FLOAT

Q: Why shouldn't I use FLOAT for money?

FLOAT is binary floating-point: it can't represent 0.1 exactly, so sums accumulate small rounding errors. Across millions of rows in a financial table, that becomes a real "the totals don't match the ledger by 17 cents" problem. DECIMAL stores exact base-10 numbers, the right tool for any value that has to sum cleanly. Always DECIMAL for money.

Q: Are DECIMAL and NUMERIC the same?

Yes, on every major engine. They're synonyms — DECIMAL(10, 2) and NUMERIC(10, 2) produce identical columns. The SQL standard treats them as equivalent. Use whichever your codebase prefers; consistency matters more than the choice.

Q: What's the difference between DECIMAL(10, 2) and DECIMAL(10)?

DECIMAL(10, 2) allows up to 10 total digits with 2 after the decimal point: values from -99,999,999.99 to 99,999,999.99. DECIMAL(10) is the same as DECIMAL(10, 0), an integer with up to 10 digits. Always specify both p and s to avoid the integer trap.

Q: When is FLOAT the right choice?

When small relative errors don't matter and you want speed. Good for: physical measurements (latitude, temperature, sensor readings), statistical aggregates that don't need to round-trip exactly (averages, KPI ratios in dashboards), ML features. Bad for: anything that has to sum cleanly across rows.

Q: Should I use FLOAT or DOUBLE?

DOUBLE (8 bytes, ~15 digits of precision) is almost always the better choice over FLOAT (4 bytes, ~7 digits). The size difference is negligible on modern hardware; the precision is meaningfully different. Some engines (Snowflake) treat them as identical anyway.

Q: How do I store percentages?

As DECIMAL if they need to sum/average cleanly (tax rates, conversion rates that aggregate to KPIs). As FLOAT if they're just for display (CPU utilization, score percentages). Document which convention you're using, and pick one for the column. Mixing 0.20 (fraction) and 20.00 (percentage) in the same column is the source of "the totals are 100x off" bugs.

Q: Can I cast FLOAT to DECIMAL safely?

Cast itself is safe (CAST(float_col AS DECIMAL(10, 2))), but you may inherit pre-existing FLOAT rounding errors before the cast. The right pattern is to store the value as DECIMAL from the start; casting at query time only papers over the precision loss already in the data.

Short answer: DECIMAL and NUMERIC are synonyms on every major engine: both are exact, fixed-precision types. FLOAT (and REAL, DOUBLE) is a binary floating-point type — fast, approximate, capable of representing huge ranges, but unable to represent simple decimals like 0.1 exactly. Money is always DECIMAL. Scientific calculations are FLOAT. Mixing them up is the source of the entire "rounding errors at month-end close" genre of incidents.

Quick comparison #

	`DECIMAL` / `NUMERIC`	`FLOAT` / `REAL` / `DOUBLE`
Storage	exact, base-10	approximate, IEEE 754 binary
Precision	up to ~38-65 digits	~7 (FLOAT/REAL), ~15 (DOUBLE)
Speed	slower (software arithmetic)	very fast (hardware arithmetic)
Range	bounded by precision	up to 1.8e308
0.1 + 0.2 = ?	0.3 exactly	0.30000000000000004
Use for	money, ratios, anything that has to sum cleanly	physics, statistics, ML features, anything where small relative error is OK

See the difference #

Loading SQL editor...

When to use which #

DECIMAL(precision, scale) — the right type for any value where exact arithmetic matters:

Money: DECIMAL(15, 2) for currency in major units, DECIMAL(15, 4) if you do per-unit pricing.
Ratios that have to round consistently: tax rates (DECIMAL(5, 4) = up to 99.9999%), commission percentages, conversion rates.
Identifiers that look numeric but aren't math: phone numbers, SSNs, postal codes. VARCHAR is usually better for these (preserves leading zeros).

FLOAT / DOUBLE PRECISION / REAL — fast, approximate, when small relative errors don't matter:

Physical measurements: latitude/longitude, temperature, sensor readings.
Statistical aggregates that don't have to round-trip exactly: averages, standard deviations, KPI ratios in dashboards.
ML features: model inputs, predictions, confidence scores.

**Choose by the operation, not the value**. A "rate" can be either: a tax rate (DECIMAL, has to sum cleanly across rows) vs. a CPU-utilization rate (FLOAT, no one cares about the 9th decimal).

Common mistakes #

FLOAT for money: the textbook mistake. SUM(amount) over a million rows produces wrong totals. Always DECIMAL.
SELECT amount = 0.1 comparing a float to a literal: rarely returns true even when "expected". Use ABS(amount - 0.1) < 0.0001 for fuzzy comparison or store as DECIMAL to avoid the question.
Forgetting to specify scale: DECIMAL(10) (no scale) means DECIMAL(10, 0), an integer. Always write DECIMAL(p, s) to be explicit.
Implicit type promotion: DECIMAL + FLOAT = FLOAT on most engines, silently losing exactness. Cast explicitly.
Postgres MONEY type: exists, but locale-dependent (the same value formats differently across servers) and discouraged. Use DECIMAL.

Dialect notes #

Postgres / DuckDB / DB2: NUMERIC and DECIMAL are synonyms; precision up to 1000+ digits. REAL (4 bytes), DOUBLE PRECISION (8 bytes).
MySQL: DECIMAL and NUMERIC synonyms, max 65 digits. FLOAT (4 bytes), DOUBLE (8 bytes). FLOAT(p) historically had quirky behavior; just use DECIMAL or DOUBLE.
SQL Server: DECIMAL / NUMERIC synonyms, max 38 digits. REAL (4 bytes), FLOAT (4 or 8 bytes depending on declared precision). MONEY and SMALLMONEY types exist but use fixed 4 decimal places, which is limiting.
Oracle: NUMBER(p, s) is the canonical numeric type — covers both decimal and float roles depending on params. BINARY_FLOAT, BINARY_DOUBLE for IEEE 754.
Snowflake: NUMBER(p, s) (synonym DECIMAL, NUMERIC), max 38 digits. FLOAT, DOUBLE, REAL are all the same — 64-bit IEEE 754.
BigQuery: NUMERIC (38 digits, 9-digit scale) for most "exact" needs; BIGNUMERIC (76 digits, 38 scale) for very high precision. FLOAT64 for floating-point. No "DECIMAL" keyword; use NUMERIC.

FAQ #

Why shouldn't I use FLOAT for money?

FLOAT is binary floating-point: it can't represent 0.1 exactly, so sums accumulate small rounding errors. Across millions of rows in a financial table, that becomes a real "the totals don't match the ledger by 17 cents" problem. DECIMAL stores exact base-10 numbers, the right tool for any value that has to sum cleanly. Always DECIMAL for money.

Are DECIMAL and NUMERIC the same?

Yes, on every major engine. They're synonyms — DECIMAL(10, 2) and NUMERIC(10, 2) produce identical columns. The SQL standard treats them as equivalent. Use whichever your codebase prefers; consistency matters more than the choice.

What's the difference between DECIMAL(10, 2) and DECIMAL(10)?

DECIMAL(10, 2) allows up to 10 total digits with 2 after the decimal point: values from -99,999,999.99 to 99,999,999.99. DECIMAL(10) is the same as DECIMAL(10, 0), an integer with up to 10 digits. Always specify both p and s to avoid the integer trap.

When is FLOAT the right choice?

When small relative errors don't matter and you want speed. Good for: physical measurements (latitude, temperature, sensor readings), statistical aggregates that don't need to round-trip exactly (averages, KPI ratios in dashboards), ML features. Bad for: anything that has to sum cleanly across rows.

Should I use FLOAT or DOUBLE?

DOUBLE (8 bytes, ~15 digits of precision) is almost always the better choice over FLOAT (4 bytes, ~7 digits). The size difference is negligible on modern hardware; the precision is meaningfully different. Some engines (Snowflake) treat them as identical anyway.

How do I store percentages?

As DECIMAL if they need to sum/average cleanly (tax rates, conversion rates that aggregate to KPIs). As FLOAT if they're just for display (CPU utilization, score percentages). Document which convention you're using, and pick one for the column. Mixing 0.20 (fraction) and 20.00 (percentage) in the same column is the source of "the totals are 100x off" bugs.

Can I cast FLOAT to DECIMAL safely?

Cast itself is safe (CAST(float_col AS DECIMAL(10, 2))), but you may inherit pre-existing FLOAT rounding errors before the cast. The right pattern is to store the value as DECIMAL from the start; casting at query time only papers over the precision loss already in the data.

Warning

The single rule that prevents 90% of numeric bugs: any column representing money is DECIMAL, end of discussion. It's slower; it doesn't matter. The cost of "the totals on the financial dashboard don't tie to the underlying ledger by 17 cents" is one tense Slack thread, three days of investigation, and a re-reconciliation. The cost of DECIMAL arithmetic is measured in microseconds. Always use DECIMAL for money. Always.